Is there a template-like expression in Sql Server?

Question

I place the alias values in sql into a string sentence and constantly generate a new string with replace.

But I have to do a lot of "replace" nested this way.

The {{name}} in the string clause is actually the alias in the sql clause

Is there a way to define the sql and template clause and automate it?

Sample:

CREATE TABLE [dbo].[message](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [quantity] [float] NULL,
    [delivery] [datetime] NULL,
    [status] [tinyint] NULL,
)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Omer', 124, CAST(N'2022-10-18T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Jacob', 548, CAST(N'2022-11-05T00:00:00.000' AS DateTime), 0)
INSERT [dbo].[message] ([name], [quantity], [delivery], [status]) VALUES ( N'Hasan', 56454, CAST(N'2022-08-09T00:00:00.000' AS DateTime), 1)
INSERT [dbo].[message] ( [name], [quantity], [delivery], [status]) VALUES ( N'Hans', 548, CAST(N'2023-11-01T00:00:00.000' AS DateTime), 0)

4 rows affected

select * from message where status=0

id	name	quantity	delivery	status
1	Omer	124	2022-10-18 00:00:00.000	0
2	Jacob	548	2022-11-05 00:00:00.000	0
4	Hans	548	2023-11-01 00:00:00.000	0

declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

select 
replace(
replace(
replace(@template
,'{{name}}',name)
,'{{quantity}}',quantity)
,'{{delivery}}',format(delivery,'dd.MM.yyy'))
from message where status=0

(No column name)
Hello, Omer todays order quantity 124 this order must be sent by the latest 18.10.2022.
Hello, Jacob todays order quantity 548 this order must be sent by the latest 05.11.2022.
Hello, Hans todays order quantity 548 this order must be sent by the latest 01.11.2023.

Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.

declare @template nvarchar(max)='Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.'
print @template

declare @sqlString nvarchar(max)='select name,quantity,format(delivery,''dd.MM.yyy'') as [delivery] from message where status=0'
print @sqlString

Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.
select name,quantity,format(delivery,'dd.MM.yyy') as [delivery] from message where status=0

/*
template embed code

*/

fiddle

Answer 1

String formatting is a job best done in the application code presentation layer rather than T-SQL. Burdening the database with such tasks limits scalability and hinders performance.

It seems your goal of your fiddle attempt is not only to avoid nested REPLACE functions but also a generalized technique to use column names from the query to indicate the values to be replaced in the template (i.e. sniff column names).

A T-SQL recursion technique will avoid nested REPLACE functions but performance will be worse. The example below uses a recursive CTE and JSON (instead of XML as in your fiddle). I'm sure there is room for improvement here but it does the job.

DECLARE @TemplateString nvarchar(MAX) = N'Hello, {{name}} todays order quantity {{quantity}} this order must be sent by the latest {{delivery}}.';
WITH 
    FormattedValues AS (
        SELECT (
            SELECT 
                  m.id
                , m.name
                , FORMAT(m.quantity, 'N2') AS quantity
                , FORMAT(m.delivery, 'dd.MM.yyy') AS delivery
            FROM dbo.message AS m
            FOR JSON AUTO
            ) AS FormattedValues_json
    )
    ,ColumnValues AS (
        SELECT 
             JSON_VALUE(FormattedValues_json.value, '$.id') AS id
            ,columns.[key] AS ColumnName 
            ,columns.[value] AS ColumnValue
            ,ROW_NUMBER() OVER(PARTITION BY JSON_VALUE(FormattedValues_json.value, '$.id') ORDER BY (SELECT 0)) AS rownum
        FROM FormattedValues
        CROSS APPLY OPENJSON(FormattedValues_json) AS FormattedValues_json
        CROSS APPLY OPENJSON(FormattedValues_json.value) AS columns
    )
    ,ReplacedValues AS (
        SELECT
              id
            , REPLACE(@TemplateString, '{{' + ColumnName + '}}', ColumnValue) AS ReplacedString
            , ColumnName
            , ColumnValue
            , rownum
        FROM ColumnValues
        WHERE rownum = 1
        UNION ALL
        SELECT
              cv.id
            , REPLACE(rv.ReplacedString, '{{' + cv.ColumnName + '}}', cv.ColumnValue) AS ReplacedString
            , cv.ColumnName
            , cv.ColumnValue
            , rv.rownum + 1
        FROM ReplacedValues AS rv
        JOIN ColumnValues AS cv ON cv.id = rv.id AND cv.rownum = rv.rownum + 1
    )
SELECT ReplacedString
FROM ReplacedValues
WHERE rownum = (SELECT MAX(rownum) FROM ColumnValues); --final string