Binaryworld - Methods to form complex strings for dynamic SQL execution ... [ SQL Server -> T-SQL ]

This T-SQL script shows several techniques to form complex dynamic SQL strings. These eliminate to some extent the coding involved in concatenating string values and making sure to add single quotes appropriately between values. The methods discussed can make the code simpler to read & is less error-prone. The solutions discussed here use similar logic i.e., using parameter markers in the dynamic SQL string and replacing them with the values using functions / SPs.

Click here to copy the following block

-- Method #1 for SQL 6.x/7.0/2000
-- This will work for strings less than or
-- equal to 255 characters only in SQL 7.0/2000.
DECLARE @cmd varchar(255)
SELECT @cmd = 'exec sp_name ''%s'', ''%s'', %s'
-- where %s represents values to replace
EXEC master..xp_sprintf @cmd OUT, @cmd,
'string value#1', 'string value#2' , '1245'
SELECT @cmd AS Replaced_Cmd
GO

-- Method #2 for SQL 6.x/7.0/2000
DECLARE @cmd varchar(255)
SELECT @cmd = 'exec sp_name ''%s1'', ''%s2'', %n'
-- where s1, s2 represents string values, %n is number
SELECT @cmd = STUFF(@cmd, CHARINDEX('%s1', @cmd) , LEN('%s1') , 'string value#1')
SELECT @cmd = STUFF(@cmd, CHARINDEX('%s2', @cmd) , LEN('%s2') , 'string value#2')
SELECT @cmd = STUFF(@cmd, CHARINDEX('%n', @cmd) , LEN('%n') , CONVERT(varchar , 1245))
SELECT @cmd AS Replaced_Cmd
GO

-- Method #3 for SQL 7.0/2000
DECLARE @cmd nvarchar(4000)
SET @cmd = N'''{call sp_name(''%s1'', ''%s2'', %n )}'''
-- where s1, s2 represents string values, %n is number
SET @cmd = REPLACE(@cmd, N'%s1', N'string value#1')
SET @cmd = REPLACE(@cmd, N'%s2', N'string value#2')
SET @cmd = REPLACE(@cmd, N'%n', CAST(1245 as nvarchar))
SELECT @cmd AS Replaced_Cmd