Expand Template with Values from Result Set

/************** Do Row Counts - Table Names in Square Brackets ***************/SET nocount ON;
USE Northwind;
GO
--***** User-Supplied Result Set #UserData:
IF OBJECT_ID(N'tempdb..#UserData', N'U') IS NOT NULL BEGIN
    DROP TABLE #UserData;
END;
SELECT [TABLE_CATALOG] AS DbName     -- User's Template variable name.
      ,[TABLE_SCHEMA]  AS SchemaName -- User's Template variable name.
      ,[TABLE_NAME]    AS TableName  -- User's Template variable name.
INTO #UserData -- Save user data into required table for later use.
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY [TABLE_NAME];
--***** User-Defined Template:
DECLARE @UserTemplate nvarchar(4000) = N'
-- Template # {_TemplateId}
SELECT count(*) as "{_TemplateId}. {TableName} RowCount"
FROM {DbName}.{SchemaName}.{TableName};
'; -- End of user's template string.
DECLARE @ConcatTemplates  nvarchar(MAX);
EXEC spExpandUserResultTemplate -- Process @UserTemplate for rows in #UserData.
    @UserTemplate       = @UserTemplate,
    @ConcatTemplates    = @ConcatTemplates OUTPUT; -- Result of All expanded templates.
PRINT @ConcatTemplates;  -- Comment or UN-comment -- Caution PRINT limit is 4K!.
PRINT '************************ Start Execution ********************************';
EXECUTE sp_executesql @ConcatTemplates;  -- Comment or UN-comment as needed.
DROP TABLE #UserData; -- Clean up temporary tables.
IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NOT NULL
    DROP TABLE #OverrideVarValueQuote;
GO

-- Template # [1]
SELECT count(*) as "[1]. [Employees] RowCount"
FROM [Northwind].[dbo].[Employees];
-- Template # [2]
SELECT count(*) as "[2]. [Categories] RowCount"
FROM [Northwind].[dbo].[Categories];
-- Template # [3]
SELECT count(*) as "[3]. [Customers] RowCount"
FROM [Northwind].[dbo].[Customers];
-- Template # [4]
SELECT count(*) as "[4]. [Shippers] RowCount"
FROM [Northwind].[dbo].[Shippers];
...

************************ Start Execution ********************************
[1]. [Employees] RowCount
-------------------------
                        9
[2]. [Categories] RowCount
--------------------------
                         8
[3]. [Customers] RowCount
-------------------------
                       91
[4]. [Shippers] RowCount
------------------------
                       3
...

-- Keep everything same, but change procedure parameters for @DefaultLeftQuote
-- and @DefaultRightQuote to override default values.
SET nocount ON;
USE Northwind;
GO
--***** User-Supplied Result Set #UserData:
IF OBJECT_ID(N'tempdb..#UserData', N'U') IS NOT NULL BEGIN
    DROP TABLE #UserData;
END;
SELECT [TABLE_CATALOG] AS DbName     -- User's Template variable name.
      ,[TABLE_SCHEMA]  AS SchemaName -- User's Template variable name.
      ,[TABLE_NAME]    AS TableName  -- User's Template variable name.
INTO #UserData -- Save user data into required table for later use.
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY [TABLE_NAME];
--***** User-Defined Template:
DECLARE @UserTemplate nvarchar(4000) = N'
-- Template # {_TemplateId}
SELECT count(*) as "{_TemplateId}. {TableName} RowCount"
FROM {DbName}.{SchemaName}.{TableName};
'; -- End of user's template string.
DECLARE @ConcatTemplates  nvarchar(MAX);
EXEC spExpandUserResultTemplate -- Process @UserTemplate for rows in #UserData.
    @UserTemplate       = @UserTemplate,
    @ConcatTemplates    = @ConcatTemplates OUTPUT, -- Result of All expanded templates.
    @DefaultLeftQuote   = '', -- Suppress default of square brackets.
    @DefaultRightQuote  = ''; -- Use no quoting characters on all variables.
PRINT @ConcatTemplates;  -- Comment or UN-comment -- Caution PRINT limit is 4K!.
-- PRINT '************************ Start Execution ********************************';
-- EXECUTE sp_executesql @ConcatTemplates;  -- Comment or UN-comment as needed.
DROP TABLE #UserData; -- Clean up temporary tables.
IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NOT NULL
    DROP TABLE #OverrideVarValueQuote;
GO

-- Template # 1
SELECT count(*) as "1. Employees RowCount"
FROM Northwind.dbo.Employees;
-- Template # 2
SELECT count(*) as "2. Categories RowCount"
FROM Northwind.dbo.Categories;
-- Template # 3
SELECT count(*) as "3. Customers RowCount"
FROM Northwind.dbo.Customers;
-- Template # 4
SELECT count(*) as "4. Shippers RowCount"
FROM Northwind.dbo.Shippers;
...

-- To enable check constraints again, just change keyword NOCHECK CONSTRAINT
-- to CHECK CONSTRAINT in user-defined template @UserTemplate and run again.
SET nocount ON;
USE Northwind;
--***** User-Supplied Result Set #UserData:
IF OBJECT_ID(N'tempdb..#UserData', N'U') IS NOT NULL BEGIN
    DROP TABLE #UserData;
END;
SELECT
    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName
    ,OBJECT_NAME(parent_object_id)      AS TableName
    ,sc.name                            AS ColumnName
    ,ck.[name]                          AS ConstraintName
    ,ck.[type_desc]                     AS ConstraintType
INTO #UserData -- Save user data into required table for later use.
FROM [sys].[check_constraints] AS ck
    INNER JOIN [sys].[columns] AS sc
        on ck.[parent_column_id] = sc.column_id
            and ck.parent_object_id = sc.object_id
ORDER BY TableName, ck.[parent_column_id]
--***** User-Defined Template:
DECLARE @UserTemplate nvarchar(4000) = N'
-- Template # {_TemplateId} - Disable check constraint, column {ColumnName}.
ALTER TABLE {SchemaName}.{TableName}
    NOCHECK CONSTRAINT {ConstraintName};
'; -- End of user's template string.
DECLARE @ConcatTemplates  nvarchar(MAX);
EXEC spExpandUserResultTemplate -- Process @UserTemplate for rows in #UserData.
    @UserTemplate       = @UserTemplate,
    @ConcatTemplates    = @ConcatTemplates OUTPUT -- Result of All expanded templates.
PRINT @ConcatTemplates;  -- Comment or UN-comment -- Caution PRINT limit is 4K!.
PRINT '************************ Start Execution ********************************';
PRINT '**** Constraint changes done.';
EXECUTE sp_executesql @ConcatTemplates;  -- Comment or UN-comment as needed.
DROP TABLE #UserData; -- Clean up temporary tables.
IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NOT NULL
    DROP TABLE #OverrideVarValueQuote;
PRINT '**** Check disable/enble for check constraints:';
SELECT
    ck.[name]                          AS ConstraintName
    ,[is_disabled]                      AS IsConstraintDisabled
FROM [sys].[check_constraints] AS ck 

-- Template # [1] - Disable check constraint, column [BirthDate].
ALTER TABLE [dbo].[Employees]
    NOCHECK CONSTRAINT [CK_Birthdate];
-- Template # [2] - Disable check constraint, column [UnitPrice].
ALTER TABLE [dbo].[Products]
    NOCHECK CONSTRAINT [CK_Products_UnitPrice];
-- Template # [3] - Disable check constraint, column [ReorderLevel].
ALTER TABLE [dbo].[Products]
    NOCHECK CONSTRAINT [CK_ReorderLevel];
-- Template # [4] - Disable check constraint, column [UnitsInStock].
ALTER TABLE [dbo].[Products]
    NOCHECK CONSTRAINT [CK_UnitsInStock];
...

************************ Start Execution ********************************
**** Constraint changes done.
**** Check disable/enble for check constraints:
ConstraintName                                IsConstraintDisabled
--------------------------------------------- --------------------
CK_Birthdate                                                     1
CK_Products_UnitPrice                                            1
CK_ReorderLevel                                                  1
CK_UnitsInStock                                                  1
... 

/**** Override quoting characters for template variable {ConstraintName}. ****/SET nocount ON;
USE Northwind;
--***** User-Supplied Result Set #UserData:
IF OBJECT_ID(N'tempdb..#UserData', N'U') IS NOT NULL BEGIN
    DROP TABLE #UserData;
END;
SELECT
    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName
    ,OBJECT_NAME(parent_object_id)      AS TableName
    ,sc.name                            AS ColumnName
    ,ck.[name]                          AS ConstraintName
    ,ck.[type_desc]                     AS ConstraintType
INTO #UserData -- Save user data into required table for later use.
FROM [sys].[check_constraints] AS ck
    INNER JOIN [sys].[columns] AS sc
        on ck.[parent_column_id] = sc.column_id
            and ck.parent_object_id = sc.object_id
ORDER BY TableName, ck.[parent_column_id]
--***** User-Defined Template:
DECLARE @UserTemplate nvarchar(4000) = N'
-- Template # {_TemplateId} - Disable check constraint, column {ColumnName}.
ALTER TABLE {SchemaName}.{TableName}
    NOCHECK CONSTRAINT {ConstraintName};
'; -- End of user's template string.
-- ***** Override Quoting Char for Specific Template Variable, {ConstraintName},
-- ***** instead of using global default quotes for variable's text replacement.
IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NOT NULL
    DROP TABLE #OverrideVarValueQuote;
CREATE TABLE #OverrideVarValueQuote (
    OverrideVarName    sysname      NOT NULL PRIMARY KEY,
    OverrideLeftQuote  nvarchar(10) NOT NULL,
    OverrideRightQuote nvarchar(10) NULL
);
INSERT INTO #OverrideVarValueQuote (OverrideVarName,
                                    OverrideLeftQuote,
                                    OverrideRightQuote)
VALUES
--     ('ConstraintName', N'', N'')  -- Override default quotes for specific variable..
    ('ConstraintName', N'''', N'''') -- Specify single quotes in text replacement.
;
DECLARE @ConcatTemplates  nvarchar(MAX);
EXEC spExpandUserResultTemplate -- Process @UserTemplate for rows in #UserData.
    @UserTemplate       = @UserTemplate,
    @ConcatTemplates    = @ConcatTemplates OUTPUT -- Result of All expanded templates.
PRINT @ConcatTemplates;  -- Comment or UN-comment -- Caution PRINT limit is 4K!.
-- PRINT '************************ Start Execution ********************************';
-- PRINT '**** Constraint changes done.';
-- EXECUTE sp_executesql @ConcatTemplates;  -- Comment or UN-comment as needed.
DROP TABLE #UserData; -- Clean up temporary tables.
IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NOT NULL
    DROP TABLE #OverrideVarValueQuote;

-- Template # [1] - Disable check constraint, column [BirthDate].
ALTER TABLE [dbo].[Employees]
    NOCHECK CONSTRAINT 'CK_Birthdate';
-- Template # [2] - Disable check constraint, column [UnitPrice].
ALTER TABLE [dbo].[Products]
    NOCHECK CONSTRAINT 'CK_Products_UnitPrice';
-- Template # [3] - Disable check constraint, column [ReorderLevel].
ALTER TABLE [dbo].[Products]
    NOCHECK CONSTRAINT 'CK_ReorderLevel';
-- Template # [4] - Disable check constraint, column [UnitsInStock].
ALTER TABLE [dbo].[Products]
    NOCHECK CONSTRAINT 'CK_UnitsInStock';
... 

USE Northwind  -- All SQL in stored procedure executed within context of
               -- database selected by end-user.

IF OBJECT_ID(N'spExpandUserResultTemplate', N'P') IS NOT NULL
    DROP PROCEDURE spExpandUserResultTemplate;
GO

/**************************************************************************/--               Procedure:  spExpandUserResultTemplate
--
-- The spExpandUserResultTemplate stored procedure will process a user-defined
-- template string that contains template variables contained in curly braces
-- (e.g. {TableName}). The text values to replace the template variables are
-- containsed in SQL RESULT SET, such as produced by SQL SELECT statement.
--
-- The SQL RESULT SET defines the mapping/association between the template
-- variable and data value to replace the template variable. (Often, a SELECT
-- statement on INFORMATION_SCHEMA views or catalog views is used to produce the
-- desired RESULT SET). Each row in RESULT SET is a set of template variable name
-- and value pairs. Column name for the result set row is automatically used as
-- template variable name. The column value is the replacement value for this
-- template variable. Thus, the user's RESULT SET defines the set of template
-- name/value pairs and each row represents one processing of the user's template.
--
-- The result of processing template string against all rows in the SQL RESULT SET
-- is stored in OUTPUT parameter that can then be PRINT-ed or executed by
-- sp_executesql.
--
-- The details about stored procedure's parameters are below:
--
--***** User-Supplied Result Set in required temporary table, #UserData:
-- Each row in result set in #UserData is a set of template variable name and value
-- pairs. Column name for the result set row is automatically used as template name
-- and column value in the row is the template value for this template name.
-- Thus, the user's result set defines the set of template name/value pairs and each
-- row represents one processing against the user's template in @UserTemplate:.
-- Example to define template variables for DbName, SchemaName, TableName:
--     IF OBJECT_ID(N'tempdb..#UserData', N'U') IS NOT NULL BEGIN
--         DROP TABLE #UserData;
--     END;
--     SELECT
--            [TABLE_CATALOG] AS DbName     -- User's Template variable name.
--           ,[TABLE_SCHEMA]  AS SchemaName -- User's Template variable name.
--           ,[TABLE_NAME]    AS TableName  -- User's Template variable name.
--     INTO #UserData -- Save user data into required table for later use.
--     FROM [INFORMATION_SCHEMA].[TABLES]
--     WHERE TABLE_TYPE in ('BASE TABLE');
--
--***** User-Defined Template, @UserTemplate: Provide template string w/variables
-- enclosed in curly braces, e.g. {TableName} will be replaced by the actual
-- table names returned in result set stored in #UserData temporary table.
--     DECLARE @UserTemplate nvarchar(4000) = N'
--     -- Template # {_TemplateId}
--     SELECT count(*) as "{_TemplateId}. {TableName} RowCount"
--     FROM {DbName}.{SchemaName}.{TableName};
--     '; -- End of user's template string.
--
-- Note: {_TemplateId} is internal variable to maintain/track a sequence number
-- for each template expansion. _TemplateId equals row count in #UserDatal
--
--***** Output parameter, @ConcatTemplates:
-- @ConcatTemplates  receives the end-result of ALL template expansions
-- for all rows contained in temporary table #UserData. After executing the
-- stored procedure, you can PRINT @ConcatTemplates to visually inspect
-- the generated text for template expansions (PRINT has 4K Unicode limit).
-- Or you can excute the value of @ConcatTemplates via sp_executesql call:
--      EXECUTE sp_executesql @ConcatTemplates;
--
--***** Quoting Char for Replacement Values: @DefaultLeftQuote, @DefaultRightQuote
-- Specify characters to surround variable's value after text replacement done.
-- Example: Place square brackets around the template values that replace the
-- template variables in the expanded template text.
-- Specify the global quoting characaters, which default to '[' and ']'.
--      @DefaultLeftQuote
--      @DefaultRightQuote
-- @DefaultLeftQuote and @DefaultRightQuote are optional parms, not required
-- in the stored procedure call.
--
--***** Override Quoting Char for Specific Variable Name:
-- If temporary table, #OverrideVarValueQuote, is present, then each row provides
-- override quotes for specific template variable, instead of global default quotes.
--     IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NOT NULL
--         DROP TABLE #OverrideVarValueQuote;
--     CREATE TABLE #OverrideVarValueQuote (
--         OverrideVarName    sysname      NOT NULL PRIMARY KEY,
--         OverrideLeftQuote  nvarchar(10) NOT NULL,
--         OverrideRightQuote nvarchar(10) NULL
--     );
--     INSERT INTO #OverrideVarValueQuote (OverrideVarName, OverrideLeftQuote, OverrideRightQuote)
--     VALUES
--         ('_TemplateId', N'', N''),  -- Override default quotes for specific variable..
--         ('TableType', N'''', N'''') -- Specify single quotes in text replacement.
--     ;
--
--***** PROCESSING NOTES:
-- If a template name or a template value is NULL, then the template expansion
-- process will convert the NULL into empty string as replacement value
-- during template replacement process.
--
-- Any template variable starting with underscore is INTERNAL VARIABLE,
-- e.g. "_TemplateId" is internal variable name for template sequence number.
--
-- If template variable name, e.g. {variable_name}, is found in @UserTemplate, but
-- NOT as a column name in the user's result set in #UserData, then no template
-- replacement is done and name enclosed in curly braces stays in the expanded
-- replacement string as output in @ConcatTemplates.
--
-- Do NOT place a GO command in template stored in @UserTemplate if you use
-- sp_executesql to execute contents of @ConcatTemplates. Using a GO comment in
-- a generated SQL script should be OK if executed by SSMS or SQLCMD utility.
--
-- Stored procedure and examples were mainly tested on SQL Server 2012 and
-- quickly checked on SQL Server 2008R2.
--
--
--
--
-- Return Status: None used.
-- Result Set of Rows Returned:  None
-- Transaction Mgt:  None done by this stored procedure.
-- Error Handling:   This procedure assumes an active TRY/CATCH block at higher
--                   level in the call tree (i.e. no TRY/CATCH in this proc).
-- Change History:
--  07/11/2016  Created.
--
--
/**************************************************************************/CREATE PROCEDURE spExpandUserResultTemplate
    -- *** Assumes user-created #UserData table exists before calling procedure.
    @UserTemplate                   nvarchar(4000),
    @ConcatTemplates                nvarchar(MAX) OUTPUT, -- Returns all expanded templates.
    @DefaultLeftQuote               nvarchar(10) = N'[',
    @DefaultRightQuote              nvarchar(10) = N']',

    -- Control level of debug information for developer to review.
    @DebugLevel                     tinyint = 0   -- Zero as default for no debugging.
                                                  -- Use 1 to print expanded templates.
                                                  -- Use 2 to display temp tables.
AS BEGIN
    SET nocount ON;  -- Exclude from generated text for template results.
    DECLARE -- Standard variables
        @Error                          int,
        @ReturnStatus                   int,
        @Msg                            varchar(255),
        @RowCount                       int;

    /**************************************************************************
    --      Validation of Input Parameters
    **************************************************************************/    IF @UserTemplate is null or @UserTemplate = '' BEGIN
        SET @Msg = 'Parameter %s can not be NULL nor empty.';
        RAISERROR(@Msg, 16, 1, '@UserTemplate') with seterror;
        RETURN -1; -- Should not reach this point due to thrown error.
    END;

    --- Test if table #UserData exists and has > 0 rows.
    IF OBJECT_ID(N'tempdb..#UserData', N'U') IS NULL BEGIN
        SET @Msg = 'Required temporary table, #UserData, does not exist.';
        RAISERROR(@Msg, 16, 1) with seterror;
        RETURN -1; -- Should not reach this point due to thrown error.
    END
    ELSE IF (SELECT count(*) from #UserData) = 0 BEGIN
        SET @Msg = 'Required temporary table, #UserData, is empty; has no rows.';
        RAISERROR(@Msg, 16, 1) with seterror;
        RETURN -1; -- Should not reach this point due to thrown error.
    END;

    /**************************************************************************
    --       Main Processing logic.
    **************************************************************************/
    -- #UserParmValues Table:  Assign Id per row of #UserData. Note each row
    -- provides one set of parameters (i.e. variable names and variable values)
    -- to perform ONE TEMPLATE EXPANSION.
    SELECT IDENTITY(int, 1, 1) AS _TemplateId,
           #UserData.*
    INTO #UserParmValues
    FROM #UserData;

    -- #VariableNameValuePairs Table:  loaded with template name/value pairs
    -- for template processing.
    CREATE TABLE #VariableNameValuePairs (
        _TemplateId     nvarchar(10),
        VariableName    sysname,
        VariableValue   nvarchar(255),-- Apply during template variable replacement.
        LeftQuote       nvarchar(10), -- Apply during template variable replacement.
        RightQuote      nvarchar(10)  -- Apply during template variable replacement.
    );
    -- Use cursor loop to combine template variable name with each template value
    -- for that variable name found in #UserParmValues. Dynamic SQL required to
    -- handle dynamic template variable names set in user's result set in #UserData.
    DECLARE @VariableName    sysname,
            @EmptyString     nvarchar(10) = N'',
            @InsertSubSelect nvarchar(4000),
            @CRLF            char(2) = CHAR(13) + CHAR(10);
    DECLARE VariableNameCursor CURSOR STATIC FORWARD_ONLY READ_ONLY FOR
        SELECT [COLUMN_NAME] AS VariableName -- Template variable names from user's
        FROM [tempdb].[INFORMATION_SCHEMA].[COLUMNS] -- result set.
        WHERE [TABLE_NAME] LIKE '#UserParmValues%' -- Avoid long dashes in names.

    OPEN VariableNameCursor;
    FETCH NEXT FROM VariableNameCursor INTO @VariableName;
    WHILE @@FETCH_STATUS = 0 BEGIN
        SET @InsertSubSelect =
              'INSERT INTO #VariableNameValuePairs (_TemplateId, VariableName, ' + @CRLF
            + '         VariableValue,  LeftQuote, RightQuote)' + @CRLF
            + 'SELECT  _TemplateId,' + @CRLF
            + '        ''' + @VariableName + ''','  + @CRLF
            + '        cast(' + @VariableName + ' as nvarchar(255)),' + @CRLF
            + '        ''' + @DefaultLeftQuote + ''',' + @CRLF
            + '        ''' + @DefaultRightQuote + ''' ' + @CRLF
            + 'FROM #UserParmValues ;'

        EXECUTE sp_executesql @InsertSubSelect;
        FETCH NEXT FROM VariableNameCursor INTO @VariableName;
    END;
    CLOSE VariableNameCursor;
    DEALLOCATE VariableNameCursor;

    UPDATE #VariableNameValuePairs  -- Handle NULL template name/values, Id size.
        SET _TemplateId = -- Ensure 3 digits w/high order zeros for proper sorting.
                          CASE LEN(_TemplateId)
                            WHEN 1  THEN N'00' + _TemplateId
                            WHEN 2  THEN N'0'  + _TemplateId
                            ELSE _TemplateId
                          END,
            -- Convert any NULL to empty string during template replacement.
            VariableValue = ISNULL(VariableValue, @EmptyString),
            VariableName  = ISNULL(VariableName, @EmptyString);

    -- If #OverrideVarValueQuote table not exists, create empty table.
    IF OBJECT_ID(N'tempdb..#OverrideVarValueQuote', N'U') IS NULL BEGIN
        CREATE TABLE #OverrideVarValueQuote (
            OverrideVarName    sysname      NOT NULL PRIMARY KEY,
            OverrideLeftQuote  nvarchar(10) NOT NULL,
            OverrideRightQuote nvarchar(10) NULL
        );
    END;

    -- UPDATE quote character with any overrides in #OverrideVarValueQuote.
    UPDATE namval
        SET LeftQuote   = oride.OverrideLeftQuote,
            RightQuote  = oride.OverrideRightQuote
    FROM #VariableNameValuePairs AS namval
    INNER JOIN #OverrideVarValueQuote AS oride
        ON namval.VariableName = oride.OverrideVarName;

    -- The delimiters put on left/right sides of the template variable name
    -- as found in @UserTemplate.
    DECLARE @VarNameLeftDelimiter  nvarchar(10) = N'{',
            @VarNameRightDelimiter nvarchar(10) = N'}';

    -- Drive replacement of template variable name with template variable value
    -- by cursor loop thru #VariableNameValue table. Order by TemplateId so that
    -- the same set of template name/value pairs are only processed against
    -- one template expansion/replacement. When TemplateId changes, save the
    -- replaced/processed template and get a fresh copy of template to process.
    SET @ConcatTemplates = @EmptyString;
    DECLARE @ExpandedTemplate   nvarchar(4000), -- One expanded template.
            @_TemplateId        nvarchar(10),
            @SaveTemplateId     nvarchar(10)   = N'-1',
            @VariableValue      nvarchar(255),
            @LeftQuote          nvarchar(10),
            @RightQuote         nvarchar(10);
    SET @ExpandedTemplate = @EmptyString;
    DECLARE TemplateNameValueCursor CURSOR STATIC FORWARD_ONLY READ_ONLY FOR
        SELECT _TemplateId, VariableName, VariableValue, LeftQuote, RightQuote
          FROM #VariableNameValuePairs
        ORDER BY _TemplateId, VariableName;
    OPEN TemplateNameValueCursor;
    FETCH NEXT FROM TemplateNameValueCursor
        INTO @_TemplateId, @VariableName, @VariableValue, @LeftQuote, @RightQuote;
    WHILE @@FETCH_STATUS = 0 BEGIN
        IF @SaveTemplateId != @_TemplateId BEGIN -- Initial start or new template?
            -- Template variables have now been expanded for one user template.
            IF @DebugLevel > 0 BEGIN
                PRINT @ExpandedTemplate;
            END;

            -- Save expanded result in big string of previously expanded templates.
            -- And reset conditions for the next template to be expanded/replaced.
            SET @ConcatTemplates  = @ConcatTemplates + @ExpandedTemplate ;
            SET @ExpandedTemplate = @UserTemplate;
            SET @SaveTemplateId   = @_TemplateId;
        END;

        SET @VariableName     = @VarNameLeftDelimiter
                                + @VariableName
                                + @VarNameRightDelimiter;
        SET @VariableValue    = @LeftQuote + @VariableValue + @RightQuote;
        SET @ExpandedTemplate = REPLACE(@ExpandedTemplate,
                                        @VariableName,
                                        @VariableValue);
        FETCH NEXT FROM TemplateNameValueCursor
            INTO @_TemplateId, @VariableName, @VariableValue,
                 @LeftQuote, @RightQuote;
    END;
    CLOSE TemplateNameValueCursor;
    DEALLOCATE TemplateNameValueCursor;

    IF @DebugLevel > 0 BEGIN  -- Print last template expansion?
        PRINT @ExpandedTemplate;
    END;

    -- Get the last expanded template and save w/other expanded templates.
    SET @ConcatTemplates = @ConcatTemplates + @ExpandedTemplate;

    IF @DebugLevel > 1 BEGIN -- Debug to display temporary tables used in script.
        PRINT '**** Table #UserData:';
        SELECT * FROM #UserData;
        PRINT ' '; PRINT '**** Table #OverrideVarValueQuote:';
        SELECT * FROM #OverrideVarValueQuote;
        PRINT ' '; PRINT '**** Table #UserParmValues:';
        SELECT * FROM #UserParmValues
        ORDER BY _TemplateId;
        PRINT ' '; PRINT '**** Table #VariableNameValuePairs:';
        SELECT * FROM #VariableNameValuePairs
        ORDER BY _TemplateId, VariableName;
    END;

    DROP TABLE #UserParmValues;
    DROP TABLE #VariableNameValuePairs;
    RETURN(0);  -- Successful exit.
END; /**** End of stored procedure spExpandUserResultTemplate ****/go