May 16, 2011 at 9:32 am
I am passing a multiple value parameter from SSRS to a Stored Procedure (SQL Server 2008). They are defined as varchar and the comma does get inserted. When I pass 1 value, I get data returned, but when I pass more than one, nothing returns. I do not get an error.
Sample:
Parameter definition in the SP:
@terms varchar (40)
I tested the SP by inserting the values within the SP,(ie.. AcademicTerm in ('Spring', 'Fall', 'Summer') and I get data when I do the test to make sure the code is working.
When I pass them from SSRS, using the parameter, (ie. AcademicTerm in (@terms). I do not get any data returned to my report in SSRS.
I have looked at suggestions and they all are suggesting adding the comma. I can see the comma is getting inserted by SSRS. I cannot understand why I get no data returned.
Any suggestions would be greatly appreciated.
Thanks.
DianeR
May 16, 2011 at 11:33 am
I think what is happening is that SQL Server is taking your parameter as a string like this:
@P in ('Spring, Fall, Summer')
You should split the string into a table first or try using XML.
May 16, 2011 at 12:17 pm
Check out this post from Jeff Moden. split function. This will get you started down the right path. This is a really common misconception about a varchar variable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2011 at 4:32 pm
OK Thanks! I appreciate the information.
May 17, 2011 at 7:15 am
Let us know if you need some help implementing. There are tons of people on here willing and able to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 18, 2011 at 6:31 am
If you have a SSRS report that has a multi-value parameter and you want to pass the selected values to a stored procedure in the form 'valuea,valueb,valuec' you can do this with the JOIN function in SSRS.
=Join(Parameters!Item.Label, ",")
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 18, 2011 at 6:45 am
Sean Lange (5/16/2011)
Check out this post from Jeff Moden. split function. This will get you started down the right path. This is a really common misconception about a varchar variable.
Not really. In this case, if you build the query in ssrs and leave it there it will work. But as soon as you convert to an sp then it fails (ssrs implements sp_executesql under the covers). That gets a lot of peoples that come "only" from a dev background.
May 18, 2011 at 6:46 am
EdVassie (5/18/2011)
If you have a SSRS report that has a multi-value parameter and you want to pass the selected values to a stored procedure in the form 'valuea,valueb,valuec' you can do this with the JOIN function in SSRS.
=Join(Parameters!Item.Label, ",")
No need, the SSRS engines does it automatically for you.
That trick is when you need to display the selected parameters on your report.
May 18, 2011 at 6:48 am
Sean Lange (5/16/2011)
Check out this post from Jeff Moden. split function. This will get you started down the right path. This is a really common misconception about a varchar variable.
Final thing. I've had TREMENDOUS performance increase by creating a temp table and dumping the split results in it before using that table either in a in() or a join. The difference is that you get the correct execution plan because of valid row estimations and stats.
I'm talking about 100 times less reads and 100 times less cpu for the same report / parameter combo.
Net time saved was around 90%.
May 18, 2011 at 6:49 am
Yes I'll shut up now :hehe:.
May 18, 2011 at 7:13 am
Ninja's_RGR'us (5/18/2011)
Sean Lange (5/16/2011)
Check out this post from Jeff Moden. split function. This will get you started down the right path. This is a really common misconception about a varchar variable.Final thing. I've had TREMENDOUS performance increase by creating a temp table and dumping the split results in it before using that table either in a in() or a join. The difference is that you get the correct execution plan because of valid row estimations and stats.
I'm talking about 100 times less reads and 100 times less cpu for the same report / parameter combo.
Net time saved was around 90%.
Excellent! I will try this out myself next time i need to use this. Also, thanks for clarifying how SSRS handles stuff. I didn't realize it was using sp_execute. Good to know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 18, 2011 at 7:21 am
Sean Lange (5/18/2011)
Ninja's_RGR'us (5/18/2011)
Sean Lange (5/16/2011)
Check out this post from Jeff Moden. split function. This will get you started down the right path. This is a really common misconception about a varchar variable.Final thing. I've had TREMENDOUS performance increase by creating a temp table and dumping the split results in it before using that table either in a in() or a join. The difference is that you get the correct execution plan because of valid row estimations and stats.
I'm talking about 100 times less reads and 100 times less cpu for the same report / parameter combo.
Net time saved was around 90%.
Excellent! I will try this out myself next time i need to use this. Also, thanks for clarifying how SSRS handles stuff. I didn't realize it was using sp_execute. Good to know.
Traces are veryy educative ;-).
Now keep in mind that this report is a dashboard... so basically download the whole db into a few 100 cells.
Before the tuning the report was consuming 2+TB of page reads. It went down to 15-20 GB after tuning (the db has 17 GB of data).
May 19, 2011 at 2:23 pm
Thank You very much for the suggestions.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply