Pass multiple values for a single paramater in SP
-
Hi All,
Could any one please let me know how to pass multiple values for a single paramater in SP.
The stored proc works fine with single value for a paramater... Im trying to use the SP in ssrs report rather writing the dataset query...
Any help on this?
Thanks
-
SSRS will pass the values through like this: 'ParamValue1, ParamValue2, ParamValue3'
You need something inside the proc to convert it to something like 'ParamValue1', 'ParamValue2', 'ParamValue3'
The splitter here will do that: http://www.sqlservercentral.com/articles/Tally+Table/72993/
If applicable, your proc will also need to change an equals to an in, e.g. :
WHERE field = @Param changes to WHERE field IN (SELECT Item FROM dbo.DelimitedSplit8k(@Param, ','))
Cheers
Gaz
-
If it's called from SSRS, it will substitute the parameter in the SQL.
So the following will work with a multi-value parameter
where ColumnName in (@YourParam)
-
Just re-read the question - I hadn't noticed it is to be passed to a SP.
My answer only works if the SQL is in the data set. For passing the parameter to a stored proc, the splitter solution given by Gazareth will be needed.
-
try using XML by passing multiple parametrs and using it in the stored procedure.
chk the below link for reference:
-
Thanks all for your reply! Will try it out.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply