MSSQL - Stored procedure to replace placeholders in texts (dynamic SQL)

It is often necessary to make the query text from constant pieces and variables.

For example we have a many tables with such structure:

(
    ID         INT IDENTITY(1,1) PRIMARY KEY,
    Number     INT,
    ChangeDate DATETIME
)

and we need dynamically, depending on variables with table name, [Number] min value and [ChangeDate] min value send queries to one or other table.
We can do it using Dynamic SQL method, for example so:

CREATE PROCEDURE MySP
    @ObjectName SYSNAME,
    @MinNumber  INT,
    @ValidFrom  DATETIME
AS BEGIN
    DECLARE @SQL NVarChar(MAX);
 
    SET @SQL = '
        SELECT
            *
        FROM
            ' + @ObjectName + '
        WHERE
            Number >= ' + CAST(@MinNumber AS VARCHAR(20)) + '
            AND
            ChangeDate >= CONVERT(DATETIME, '''
            + CONVERT(VARCHAR(30), @ValidFrom, 121) + ''', 121);';
 
    PRINT @SQL;
 
    EXEC sp_executesql @SQL;
END;
GO
 
EXEC MySP 'MyTableNr1', 123, '2015-01-01 12:00';

It works, but SQL-Text format is not readable.
Much more readable looks the text converted to a template with placeholders:

'
SELECT
    *
FROM
    {ObjectName}
WHERE
    Number >= {MinNumber}
    AND
    ChangeDate >= CONVERT(DATETIME, ''{ValidFrom}'';
'

In this case our example stored procedure will look like this:

CREATE PROCEDURE MySP
    @ObjectName SYSNAME,
    @MinNumber  INT,
    @ValidFrom  DATETIME
AS BEGIN
    DECLARE @SQL NVarChar(MAX);
 
    SET @SQL = '
        SELECT
            *
        FROM
            {ObjectName}
        WHERE
            Number >= {MinNumber}
            AND
            ChangeDate >= CONVERT(DATETIME, ''{ValidFrom}'';'
 
    SET @SQL = REPLACE(@SQL, '{ObjectName}', @ObjectName);
    SET @SQL = REPLACE(@SQL, '{MinNumber}',  CAST(@MinNumber AS VARCHAR(20)));
    SET @SQL = REPLACE(@SQL, '{ValidFrom}',  CONVERT(VARCHAR(30), @ValidFrom, 121));
 
    PRINT @SQL;
 
    EXEC sp_executesql @SQL;
END;

It is good for so simple example, but if we will have more dynamical parameters and inside one stored procedure (or plain query) more then one template, then this query will have too many redundant call like this:

SET @SQL = REPLACE(@SQL, ..., ...);

The solution is to create some help procedure which will take text template and dictionary with placeholder name:value pairs as parameters.
Stored procedure can not take table-value parameters directly.
Table-valued parameters have to be declared by using user-defined table types.
In our case:

CREATE TYPE PlaceholderMappingType AS TABLE (
    Placeholder NVARCHAR(100),
    Value NVARCHAR(MAX)
);

The complete script with usage example:

USE tools;
GO
IF OBJECT_ID('tools.dbo.sp_ReplacePlaceholders') IS NOT NULL BEGIN
    PRINT '-- DROP PROCEDURE dbo.sp_ReplacePlaceholders';
    DROP PROCEDURE dbo.sp_ReplacePlaceholders;
END;
GO
IF EXISTS (
    SELECT *
    FROM tools.INFORMATION_SCHEMA.DOMAINS
    WHERE DOMAIN_NAME = 'PlaceholderMappingType'
) BEGIN
    PRINT '-- DROP TYPE dbo.PlaceholderMappingType';
    DROP TYPE dbo.PlaceholderMappingType;
END;
GO
PRINT '-- CREATE TYPE dbo.PlaceholderMappingType';
-- TABLE-Domain (User Defined Type) for placeholders dictionary
CREATE TYPE PlaceholderMappingType AS TABLE (
    Placeholder NVARCHAR(100),
    Value NVARCHAR(MAX)
);
GO
CREATE PROCEDURE dbo.sp_ReplacePlaceholders (
    @Text                NVARCHAR(MAX) OUTPUT,
    -- TABLE-Domain (User Defined Type) for placeholders dictionary
    @PlaceholderMappings PlaceholderMappingType READONLY,
    -- Flag to replace in placeholder values each single apostrof with double apostrof
    @EscapeApostrofs     BIT = 1,
    -- Text to TrimLeft in each text line
    @IndentString        VARCHAR(MAX) = '',
    -- Flag to PRINT additional diagnostic information
    @Debug               BIT = 0
)
AS BEGIN
    SET NOCOUNT ON;
 
    IF (@Debug = 1)
        PRINT '-- EXEC dbo.sp_ReplacePlaceholders'
 
    DECLARE
        @CRLF             CHAR(2) = CHAR(13)+CHAR(10),
        @Indent_CharIndex INT,
        @I                INT = 0,
        @Char             CHAR(1),
        @Indent           VARCHAR(MAX) = '';
 
    -- Trim rows left
    IF (CHARINDEX(@IndentString, @Text, 0) = 1)
        SET @Text = STUFF(@Text, 1, LEN(@IndentString), '');
    SET @Text = REPLACE(@Text, @CRLF + @IndentString, @CRLF);
 
    -- Declare FOREACH(placeholderRow IN @PlaceholderMappings) cursor
    DECLARE
        @Placeholder NVARCHAR(102),
        @Value       NVARCHAR(MAX);
    DECLARE Placeholders_Cursor CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT
        Placeholder = '{' + REPLACE(REPLACE(Placeholder, '{', ''), '}', '') + '}',
        Value       = CASE @EscapeApostrofs
                          WHEN 1
                          THEN REPLACE(Value, '''', '''''')
                          ELSE Value
                      END
    FROM @PlaceholderMappings;
 
    OPEN Placeholders_Cursor;
 
    FETCH NEXT FROM Placeholders_Cursor INTO @Placeholder, @Value;
    WHILE @@FETCH_STATUS = 0 BEGIN
        
        IF (@Debug = 1) AND (CHARINDEX(@Placeholder, @Text) = 0)
            PRINT '--        ==> Could not find Placeholder ' + @Placeholder;
        SET @Text = REPLACE(@Text, @Placeholder, @Value);
 
        FETCH NEXT FROM Placeholders_Cursor INTO @Placeholder, @Value;
    END;
 
    CLOSE Placeholders_Cursor
    DEALLOCATE Placeholders_Cursor
 
    RETURN;
END;
GO
DECLARE
    @SQL NVARCHAR(MAX),
    @PlaceholderMappings PlaceholderMappingType;
 
SET @SQL = '
        SELECT
            F1=''{aaa}'',
            F2=''{bbb}'',
            F3=''{ddd}'';';
INSERT INTO @PlaceholderMappings
VALUES
    ('aaa',   'A''A''A'),
    ('{bbb}', 'BBB'),
    ('ccc',   'CCC');
 
DECLARE @IndentString VARCHAR(MAX) = SPACE(8);
EXEC dbo.sp_ReplacePlaceholders
    @SQL OUTPUT,
    @PlaceholderMappings,
    @IndentString=@IndentString,
    @Debug=1;
PRINT '>>>' + @SQL + '<<<';