Hi all
I need your help, how can you format a string with placeholders with the number of placeholders not known in advance (dynamic number of placeholders) ?
I need to do that in T-SQL
"xxxxx {0} yyyy {1} zzzz {2} "
with {} replaced by parameters.
I did the code:
Code:
DECLARE @PARAMETERS nvarchar(500)
DECLARE @TEST nvarchar(500)
DECLARE @QUERY nvarchar(500)
SET @TEST = '%s %s %s'
SET @PARAMETERS='o,p,i'
SET @QUERY = '
PRINT @TEST;
EXEC xp_sprintf @TEST out, @TEST,' + @PARAMETERS + '
PRINT @TEST'
EXECUTE sp_executesql @QUERY,N'@TEST nvarchar(500), @PARAMETERS nvarchar(500)', @TEST,@PARAMETERS
That's working but... I had to put in concatenation @PARAMETERS (not a good practice)
I get the result:
%s %s %s
o p i
I tried to do this, so as to avoid SQL injection risk by injecting the parameters directly in the dynamic query.
Code:
DECLARE @PARAMETERS nvarchar(500)
DECLARE @TEST nvarchar(500)
DECLARE @QUERY nvarchar(500)
SET @TEST = '%s %s %s'
SET @PARAMETERS='o,p,i'
SET @QUERY = '
PRINT @TEST;
EXEC xp_sprintf @TEST out, @TEST,@PARAMETERS
PRINT @TEST'
EXECUTE sp_executesql @QUERY,N'@TEST nvarchar(500), @PARAMETERS nvarchar(500)', @TEST,@PARAMETERS
I got the message:
%s %s %s
Error executing extended stored procedure: Invalid Parameter
Msg 50003, Level 1, State 0
%s %s %s
Please help
Do you know a better way of doing things too?
Thanks
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
