How do I pass a Multi-Value parameter to a stored procedure?
Hi
I have an SSRS report that executes a stored procedure with several parameters. I need to make these parameters multi-value but I don't know how to do this. Even if I make the parameter multi-value but only send one value across I get a scaler variable error.
Thanks
David
October 24th, 2008 3:10am
Hello David,
In your sproc add a filter condition like
WHERE Parameter in (@Parameter)
Hope this helps
Free Windows Admin Tool Kit Click here and download it now
October 24th, 2008 3:17am
Thanks for the response
My filters are set as you describe. I think my problem is to do with how to format the parameter being sent.
I have just created a simple procedure -'select * from <table> where<column>in (@Parameter)'
When I execute it in SQL Server Console (as opposed to running a query to execute it) I can't work out the correct format to pass it multiple parameters. I get an error when I send 2 parameters separated by a comma
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
David
October 24th, 2008 7:08am
Hello,
I think you have more than one parameters .Then You have to execute the sproc like
sproc_name 'param1','param2','val1,val2,val3','param4'
where parameter3 has multiplevalues.
Free Windows Admin Tool Kit Click here and download it now
October 24th, 2008 7:39am
Multi-value parameters are passed to SQL stored procs as a comma-delimited string of the values. You need to then parse this back into values in your stored proc. SQL does not support passing multiple values to a stored proc natively.
October 24th, 2008 7:25pm
Aaron Meyers [MSFT] wrote:
Multi-value parameters are passed to SQL stored procs as a comma-delimited string of the values. You need to then parse this back into values in your stored proc.
This link demonstrates how to parse(split) a comma-limited string into a tableby applying table-valued function.
Let us know if helpful.
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2008 4:13pm
I think you have to use the function join of reporting services, like join(X, "/") and if the parameter X have 2 values like "Bonjour" and "Test", join(X,"/")="Bonjour/Test"So you can use this in your procedure. Create a Dataset as text and concatenate the values as you want.DATASET:="execute MaProcedure "+join(X,"/")Hope it helps
October 26th, 2008 8:59am
Thank you all for your ideas. I'll have a go at the function.
David
Free Windows Admin Tool Kit Click here and download it now
October 27th, 2008 5:05am
Hi folks
It seems to be working now - many thanks to you all
I created 2 functions as per above - 1 to handle integer and 1 for char values
I set all my input parameters as varchar
I turned the parameters in the report dataset to join(x etc). Running the dataset query directly did not need this but to run as a report it does
Once again, many thanks
David
October 27th, 2008 11:27am
This seems to be what i am looking for, can someone give me the syntax for the text command? ="execute MaProcedure "+join(X,"/") this does not work I am using SQL server 2008ThanksZafar
Free Windows Admin Tool Kit Click here and download it now
December 16th, 2008 11:59am
I implemented SQLUSA solution using hidden parameters. Please see report 'Multi-values parameters' from http://www.californiadreamhomesandland.com/Pages/ReportExplorer.aspx?ItmPath_25B7ED104E83=/English/Web%20Master/William but I use IN (@parameter) for report 'For test only'. Can't see a big difference. Alexei Cioina
December 16th, 2008 12:25pm
David,
I've exact same issue. could you pls. let me know how did you resolve this issue of passing multi parameters comma delimited.
like 1,2,3SSRSRpt
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2011 3:43pm
David,
I've exact same issue. could you pls. let me know how did you resolve this issue of passing multi parameters comma delimited.
like 1,2,3
SSRSRpt
Hi All, I've exact same issue. Anyone can tell me the solution in details (solution step by step, please)? Thanks a lot!
October 6th, 2011 4:52am
Thanks guysThanks Shiven:) If Answer is Helpful, Please Vote
Free Windows Admin Tool Kit Click here and download it now
July 5th, 2012 11:14pm