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

This page was last updated on May 01, 2006 04:28 PM.