June 5, 2017 at 10:31 am
I have a stored procedure that calls other stored procedures. All of the other stored procedures return integers and are stored as variables in the main SP. When I run the main stored procedure in SSRS, it returns back with an ID and not the result set. However, when running the main stored procedure in SSMS, I get the correct result set back.
For testing purposes, when I hard code the results in the local variables of the main SP without calling the other SPs , the SSRS report works fine.
Is there an easy resolution to this? One post suggested to return an output variable in the other stored procedures. However, the other SPs are used by other applications and therefore will break other things.
Below is the call from the main SP to the other SPs and the results being stored in 3 variables of type int.
exec @dtEffective = product..selUpdEnv 'PrevBusDt','=',@cdUser,'N'
exec @dtCurBus = product..selUpdEnv 'CurBusDt','=',@cdUser,'N'
exec @dtCstBasStart = product..selUpdEnv 'CstBasStart','=',@cdUser,'N'
Below is the return value from selUpdEnv. And @success is of type int.
return @success
Appreciate the help.
June 5, 2017 at 11:43 am
rs80 - Monday, June 5, 2017 10:31 AMI have a stored procedure that calls other stored procedures. All of the other stored procedures return integers and are stored as variables in the main SP. When I run the main stored procedure in SSRS, it returns back with an ID and not the result set. However, when running the main stored procedure in SSMS, I get the correct result set back.For testing purposes, when I hard code the results in the local variables of the main SP without calling the other SPs , the SSRS report works fine.
Is there an easy resolution to this? One post suggested to return an output variable in the other stored procedures. However, the other SPs are used by other applications and therefore will break other things.
Below is the call from the main SP to the other SPs and the results being stored in 3 variables of type int.
exec @dtEffective = product..selUpdEnv 'PrevBusDt','=',@cdUser,'N'
exec @dtCurBus = product..selUpdEnv 'CurBusDt','=',@cdUser,'N'
exec @dtCstBasStart = product..selUpdEnv 'CstBasStart','=',@cdUser,'N'
Below is the return value from selUpdEnv. And @success is of type int.
return @success
Appreciate the help.
I am pretty sure you would want your main stored procedure (the one that calls the other 3 procedures) to have 3 output parameters - @dtEffective, @dtCurBus, and @dtCstBasStart.
Add those as output parameters and that should fix your issues. You shouldn't need to modify selUpdEnv.
If you cannot modify the main SP, then make a new one with the 3 output parameters and use that for your report.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 5, 2017 at 11:54 am
Thanks for your reply.
The 3 variables @dtEffective, @dtCurBus, and @dtsCstBasStart are being used within the SP after the SPs store the results in the variables. Should that cause any problems?
June 5, 2017 at 12:49 pm
rs80 - Monday, June 5, 2017 10:31 AMI have a stored procedure that calls other stored procedures. All of the other stored procedures return integers and are stored as variables in the main SP. When I run the main stored procedure in SSRS, it returns back with an ID and not the result set. However, when running the main stored procedure in SSMS, I get the correct result set back.For testing purposes, when I hard code the results in the local variables of the main SP without calling the other SPs , the SSRS report works fine.
What can happen in SSRS or SSIS at design time when calling a stored proc that has multiple queries in it, is that the results of the first query get assumed to be metadata of the final results. You can avoid this behavior by adding the following before the statement calling the stored proc:SET FMTONLY OFF;
June 5, 2017 at 1:06 pm
rs80 - Monday, June 5, 2017 11:54 AMThanks for your reply.The 3 variables @dtEffective, @dtCurBus, and @dtsCstBasStart are being used within the SP after the SPs store the results in the variables. Should that cause any problems?
You can name the output parameters anything you want. I just picked those as I saw them in there.
If those are the values you are wanting to return to the calling application, then might as well reuse them. But no harm in making more (well, except more tempdb use).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply