April 2, 2014 at 10:02 am
I have a stored procedure that accepts two float parameter values used to filter results. I want to allow the end user to enter the values in either order in case they make a mistake. For example, if they want all the rows that have a score value between 40.0 and 60.0 I want to allow them to select 60.0 as the minimum value and 40.0 as the maximum value (a mistake) and still get the proper results. I check the two parameters and swap them if they're in the wrong order within the stored procedure. This works great if I execute the stored procedure. If I select the wrong-ordered values in the report I don't get any rows back, the same as would happen before I added the code to swap the values.
Is there anything I need to do to allow SSRS to work with this? I did run the stored procedure within the Query Designer and it worked fine.
Not sure if you need to see this for this problem but here's a snippet. Please let me know if I should provide any other info.
ALTER PROCEDURE [dbo].[TestAScoreRange] @MinimumCompositeScore float, @MaximumCompositeScore float
AS
begin
SET NOCOUNT ON
declare @Swapem float
if @MinimumCompositeScore > @MaximumCompositeScore
select @swapem = @MaximumCompositeScore ,
@MaximumCompositeScore = @MinimumCompositeScore,
@MinimumCompositeScore = @Swapem
select *
from ...
Thanks for reading.
April 2, 2014 at 11:53 am
That is rather strange but it should work fine.
_______________________________________________________________
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/
April 2, 2014 at 12:15 pm
Thanks. Any advice for figuring out why it's not working through the report (in Visual Studio - I haven't published the parameterized version to Report Manager yet)?
April 2, 2014 at 12:25 pm
pharmkittie (4/2/2014)
Thanks. Any advice for figuring out why it's not working through the report (in Visual Studio - I haven't published the parameterized version to Report Manager yet)?
If you haven't published it, it isn't on your sql instance. Your report is only going to see whatever version of the proc is actually on the server.
_______________________________________________________________
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/
April 2, 2014 at 12:39 pm
Right now everything is on my workstation. I am only accessing my local instance of SQL Server for SSRS and for running queries via SSMS. This is how I test before I publish to a true server. Even the true server isn't production. Sorry my last comment added confusion. Thanks.
April 2, 2014 at 2:13 pm
pharmkittie (4/2/2014)
Right now everything is on my workstation. I am only accessing my local instance of SQL Server for SSRS and for running queries via SSMS. This is how I test before I publish to a true server. Even the true server isn't production. Sorry my last comment added confusion. Thanks.
Ahh gotcha. Well if your updated stored proc is already on the server with the report there is no reason it shouldn't work. A stored procedure is not going to behave differently based on what calls it. That leads me to believe there must be something else going on here.
_______________________________________________________________
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/
April 3, 2014 at 8:39 am
Doh! I had filters on (in Visual Studio: Right click on dataset, select dataset properties then filter from left menu). The logic was negating my efforts in the stored procedure. I deleted the filters and everything is now working as expected. That's what I get for starting to work on this a month ago and just coming back to it to finish it up. Forgot I used filters - I don't usually use them.
Thanks Sean for your help.
April 3, 2014 at 8:42 am
pharmkittie (4/3/2014)
Doh! I had filters on (in Visual Studio: Right click on dataset, select dataset properties then filter from left menu). The logic was negating my efforts in the stored procedure. I deleted the filters and everything is now working as expected. That's what I get for starting to work on this a month ago and just coming back to it to finish it up. Forgot I used filters - I don't usually use them.Thanks Sean for your help.
No problem. Glad you were able to figure it out. 😀
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply