Expand Template with Values from Result Set

,

Overview and Background

The author wanted a tool to generate SQL statements for those cases not easily supported in SQL Server Management Studio (SSMS). SSMS provides features to:

  1. Script out schema objects as DDL statements, such as CREATE TABLE.
  2. Expand SSMS templates by manually entering template values for each template expansion.
  3. Request SSMS to generate INSERT, DELETE, SELECT, or UDPATE statements for a specific table.

Or, if not supported by SSMS, search for a utility script hardcoded to generate a specific SQL statement, e.g. foreign key constraints or triggers. The two MS tools, sp_msforeachdb and sp_msforeachtable, can be executed; however, these tools are limited to one template variable.

Author's design goals were to make a tool:
  1. Simple for SQL programmer to write a template string containing variables.
  2. Quick to specify variable values for expansion of template variables.
  3. Easy to process template many times, e.g. for every table in database.

The spExpandUserResultTemplate stored procedure processes a user-defined template string that contains template variables enclosed in curly braces, e.g. {TableName}. The text values to replace the template variables are contained in SQL result set, such as produced by a SELECT statement.

The SQL result set defines the association between the template variable and data value to replace the template variable. (Often, a SELECT statement on INFORMATION_SCHEMA views or catalog views 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 complete set of template name/value pairs and each row represents one processing of the user's template.

The result of processing the template string against all rows in the SQL result set is stored in an OUTPUT parameter that can then be PRINT-ed or executed by sp_executesql at the user's option.

Parameters

The parameters and temporary tables expected by the procedure spExpandUserResultTemplate are provided in the table below.

Parameters and Temp Tables Information
User-Supplied Result Set in Temporary Table, #UserData
Each row in #UserData is a set of template variable name and value pairs. Column name for #UserData table is automatically used as template variable name and column value is the template value for this template variable.
User-Defined Template, @UserTemplate
@UserTemplate supplies the template string having variable names enclosed in curly braces. For example, "{TableName}" will be replaced by the actual table names returned in result set stored in #UserData.
Output Parameter of Expanded Templates, @ConcatTemplates
@ConcatTemplates receives the end-result of ALL template expansions for all rows contained in #UserData. After executing the stored procedure, you can PRINT @ConcatTemplates to inspect the generated text for template expansions. Or you can excute sp_executesql call on the value of the @ConcatTemplates OUTPUT parameter.
Quoting Character for Replacement Values: @DefaultLeftQuote, @DefaultRightQuote
Two parameters specify the characters to surround a variable's value after text replacement is done. Example: Place square brackets around the template values in the expanded template text. Default to '[' and ']'.
Override Quoting Character for Specific Variable Name in Temporary Table, #OverrideVarValueQuote
If temporary table, #OverrideVarValueQuote, exists, then each row provides override quotes for specific template variable, instead of global default quotes in @DefaultLeftQuote and @DefaultRightQuote.

More details about parameters and processing notes can be found in the comment header for the stored procedure spExpandUserResultTemplate.

Examples

The examples below provide more insight into how to invoke this stored procedure by examining the actual usage to produce SQL statements for different purposes. The expanded results have been shortened to show the first few rows in the result set generated by stored procedure.

Example - Get Row Counts - Table Names in Square Brackets

For the expansion of many SQL statements/templates, using the global defaults of square brackets is sufficient and a simpler interface to invoke. The other parameters are provided to handle the less frequent or unusual situation, as illustrated in the examples below.
/************** 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

The expanded results for the template:

-- 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];
...

After execution by sp_executesql, the partial results are below.

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

Example - Get Row Counts - Table Names Without Brackets

-- 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

The expanded results foir the template:

-- 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;
...

Example - Disable ALL CHECK constraints

-- 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 

The expanded results for the template:

-- 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];
...

After execution by sp_executesql, the partial results are below.

************************ 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
... 

Example - Disable Check Constraints -  Override default quotes for specific template variable

/**** 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;
The expanded results for the template.
-- 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';
... 

Concluding Thoughts and Suggestions

  1. Be sure to create the stored procedure, spExpandUserResultTemplate, in the correct database. Change the "USE Northwind" statement in the stored procedure script.

  2. The internal template variable, {_TemplateId}, can be useful to track the PRINT-ed SQL statements and the results of the executed SQL statements.

  3. Be creative in how you build the result set in temporary table #UserData. INFORMATION_SCHEMA views or catalog views are handy sources of metadata.

  4. The EXAMPLEs show template expansions for SQL statements. Using the @UserTemplate parameter to hold a generalized text template (non-SQL) would be an interesting to explore.

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

Rate

Share

Share

Rate