Sample Header Ad - 728x90

Is there a template-like expression in Sql Server?

1 vote
1 answer
381 views
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)
status
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. |
status
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
status
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](https://dbfiddle.uk/amAM01de)
Asked by omerix (101 rep)
Oct 17, 2022, 08:21 AM
Last activity: Oct 17, 2022, 02:50 PM