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?
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) )
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?