question

nhill7873 avatar image
nhill7873 asked

Using stored procedure with Parameters in SSRS and getting no data returned

I am new to SSRS, my reports run well while using queries for datasets....but when I use a stored procedure for a dataset and set up my parameters no data is returned. I am not sure what I am doing wrong. When I catch it in SQL server profiler, it looks like the parameters are not passing in the right info compared to the same crystal report that I am trying to replace. Any Ideas?
stored-proceduresssrs-2008-r2parametersbidsdatasets
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have the parameters mapped to the right values in your datasets? Are your parameters passing in single values or allowing multiple selections? Without really seeing your screen it is a bit difficult to pick out the exact problem.
2 Likes 2 ·
A good walk through on using parameters to just double check everything is in the right order http://ssrstutorials.blogspot.com/2012/07/lesson-7-creating-ssrs-parameterized.html?m=1
2 Likes 2 ·
Have you tested the stored procedure outside of SSRS? What object type are you using to execute the procedure?
0 Likes 0 ·
Don't forget to vote on the answers you've received and mark one as a solution.
0 Likes 0 ·
SimonThompson avatar image
SimonThompson answered
You should not need to create your parameters in SSRS if you are basing them on a SP as they are created automatically. The other thing to bear in mind is that if you 'allow multiple' choices on your parameter that wont work when using sp. Are you allowing multiple?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SimonThompson avatar image
SimonThompson answered
If you are allowing multiple then this wont work in the SP CREATE PROC usp_Custsincity ( @mycities NVARCHAR(250) ) AS SELECT * FROM mytable WHERE city IN ( @mycities ) Assume you selected London Paris and Hamilton in SSRS, SSRS passes this string back to the SP "London,Paris,Hamilton" The SP will interpret this as you looking for a city called "London,Paris,Hamilton" as apposed to it looking for any of the three. I have used a split function before to solve it, resulting in the SP being CREATE PROC usp_Custsincity ( @mycities NVARCHAR(250) ) AS SELECT * FROM mytable WHERE city IN ( SELECT * FROM split(@mycities) )
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.