SET NOCOUNT ON

DECLARE @CommitTransaction  INT               SET @CommitTransaction = 1 -- AS COMMIT_TRANSACTION
DECLARE @Env                VARCHAR(64)       SET @Env =  CASE dbo.fn_someFunc
                                                               WHEN 'staging' 	  THEN 'UAT'
                                                               WHEN 'production'  THEN 'PROD'                                                               
                                                               ELSE dbo.fn_someFunc()
                                                          END

DECLARE @appname          VARCHAR(50)       SET @appname        = APP_NAME()
DECLARE @time             DATETIME          SET @time           = GETDATE()
DECLARE @user             INT               SET @user           = dbo.fn_someUser()
DECLARE @site             VARCHAR(50)       SET @site           = HOST_NAME()


DECLARE @values TABLE (appname VARCHAR(MAX), section VARCHAR(MAX), name VARCHAR(255), value VARCHAR(MAX))
INSERT INTO  @values
SELECT       @appname,    'section1',    'setting1',     '42'
UNION SELECT @appname,    'section2',    'setting2',     'some_value'


SELECT vs.appname,
       'Error: Unknown app'      AS Status
FROM @values                     AS vs
     LEFT JOIN dbo.applications  AS app ON app.appname = vs.appname
WHERE app.appId IS NULL

SELECT 'Not changed'               AS Status,
        app.appname,
        vs.section + '/' + vs.name AS ConfigName,
        ac.configValue             AS Value
FROM    @values                            AS vs
        INNER JOIN dbo.applications        AS app ON app.appname = vs.appname
        LEFT JOIN dbo.applicationConfig    AS ac  ON ac.appId = app.appId
                                                     AND vs.section = ac.section
                                                     AND vs.name = ac.configName
WHERE ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
       OR
       (vs.value IS NULL AND ac.configValue IS NULL))


SELECT 'Going to change'          AS Status,
       app.appname,
       vs.section + '/' + vs.name AS ConfigName,
       ac.configValue             AS OldValue,
       vs.value                   AS NewValue
FROM   @values                           AS vs
       INNER JOIN dbo.applications       AS app ON app.appname = vs.appname
       LEFT JOIN dbo.applicationConfig   AS ac  ON ac.appId = app.appId
                                                   AND vs.section = ac.section
                                                   AND vs.name = ac.configName
WHERE NOT ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
           OR
           (vs.value IS NULL AND ac.configValue IS NULL))


IF EXISTS ( SELECT 1
            FROM @values                           AS vs
                 INNER JOIN dbo.applications       AS app ON app.appname = vs.appname
                 LEFT JOIN dbo.applicationConfig   AS ac ON ac.appId = app.appId
                                                            AND vs.section = ac.section
                                                            AND vs.name = ac.configName
            WHERE NOT ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
                       OR
                       (vs.value IS NULL AND ac.configValue IS NULL))
           )
BEGIN
    BEGIN TRY
    BEGIN TRANSACTION

        SET NOCOUNT OFF

        PRINT 'Deleting config values...'
        DELETE FROM dbo.applicationConfig
        WHERE configId IN ( SELECT ac.configId
							FROM @values                             AS cv
								 INNER JOIN dbo.applications         AS app ON app.appname = vs.appname
								 INNER JOIN dbo.applicationConfig    AS ac  ON ac.appId = app.appId
																			   AND vs.section = ac.section
																			   AND vs.name = ac.configName
							WHERE NOT ((vs.value IS NOT NULL AND ac.configValue IS NOT NULL AND vs.value = ac.configValue)
									   OR
									   (vs.value IS NULL AND ac.configValue IS NULL))
                           )

        PRINT 'Inserting new config values...';
        INSERT INTO dbo.applicationConfig
        (
            appId,
            section,
            configName,
            configValue,
            appname,
            time,
            user,
            site
        )
        SELECT app.appId,
               vs.section,
               vs.name,
               vs.value,
               @appname,
               @time,
               @user,
               @site
        FROM @values as vs
             INNER JOIN dbo.applications        AS app ON app.appname = vs.appname
             LEFT JOIN dbo.applicationConfig    AS ac  ON ac.appId = app.appId
                                                          AND vs.section = ac.section
                                                          AND vs.name = ac.configName
        WHERE vs.value IS NOT NULL AND ac.configValue IS NULL


        PRINT 'Checking result:'
        SELECT app.appname,
               vs.section + '/' + vs.name AS ConfigName,
               ac.configValue             AS NowValue,
               vs.value                   AS ExpectedValue
        FROM @values as vs
             INNER JOIN dbo.applications        AS app ON app.appname = vs.appname
             LEFT JOIN dbo.applicationConfig    AS ac  ON ac.appId = app.appId
                                                          AND vs.section = ac.section
                                                          AND vs.name = ac.configName

        IF @CommitTransaction = 1
            COMMIT TRANSACTION

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            SELECT 'Warning: TRANSACTION ROLLED BACK on ERROR' AS Error,
                    ERROR_MESSAGE()                            AS ErrorMessage
            ROLLBACK TRANSACTION
            EXEC dbo.sp_errorHandler;
    END CATCH
END