Calling Stored Procedures Dynamically
Hi I am having a problem when trying to call a stored procedure using a parameter to supply the name of the procedure. I have two reports (A and B) that are linked with an action on a text box in report A. On clicking a text box (in report A) a parameter is set in report B, I am using this parameter to 'name' a stored procedure that I want to call in report B. i.e. Select or enter stored procedure name: [@ParameterName] When I preview my report and click on the text box with the action defined on it, it takes me to report B, sets the parameter and brings back the correct number of records but with no data in them. I think the issue stems from being unable to define the fields in design mode due to the procedure not being named until the text box in report A has been clicked. I have tried manually defining a field for the dataset (in report B) and setting its firled source to the name of the field that will come through the procedure but this returns an error. Any help would be very much appreciated.
August 6th, 2012 6:28am

Hello DaveH984, after reading your question, i understand that, you are calling report B from action method of textbox in report A. but calling SP !!!?? SP is used to populate dataset, and you can not populate dataset dynamically as per my knowledge. you can not do such thing in SSRS. let me know if i understand wrong.Touseef - CrazyErrors
Free Windows Admin Tool Kit Click here and download it now
August 6th, 2012 6:46am

Hi Touseef Thanks for your reply. I think you do understand correctly and you may well be right that you cannot populate a dataset dynamically in this way. It was the way that it brought back the right number of rows, even though they were blank that made mee think it may be possible. I think I may have found a work around to this issue of Dynamically calling Stored Procedures. I have created a master proc which is effectively the following: Create Proc MasterProc @Procname VarChar(100) as Begin create table #temp (Field1) Exec @ProcName Select Field1 from #temp End You can then call this proc with the @ProcName as a parameter of MasterProc that is passed from report A to report B, effectively creating a dynamic way of selecting a proc. The obvious complication with this is that the #temp table has to be able to accomodate the output from any proc (@ProcName) you wish to put in. SSRS objects can then be hidden depending on the Paramaeter value (@ProcName) passed from reort A to B. In my case this is ok but if you have any ideas of a better way of doing this they would be very welcome.
August 6th, 2012 8:11am

Hi Touseef Thanks for your reply. I think you do understand correctly and you may well be right that you cannot populate a dataset dynamically in this way. It was the way that it brought back the right number of rows, even though they were blank that made mee think it may be possible. I think I may have found a work around to this issue of Dynamically calling Stored Procedures. I have created a master proc which is effectively the following: Create Proc MasterProc @Procname VarChar(100) as Begin create table #temp (Field1) Exec @ProcName Select Field1 from #temp End You can then call this proc with the @ProcName as a parameter of MasterProc that is passed from report A to report B, effectively creating a dynamic way of selecting a proc. The obvious complication with this is that the #temp table has to be able to accomodate the output from any proc (@ProcName) you wish to put in. SSRS objects can then be hidden depending on the Paramaeter value (@ProcName) passed from reort A to B. In my case this is ok but if you have any ideas of a better way of doing this they would be very welcome.
Free Windows Admin Tool Kit Click here and download it now
August 6th, 2012 8:11am

Hi DaveH984, I am glad to hear that you have got the issue resolved by yourself. Thanks very much for your sharing. That's very helpful. Could you mark your reply as answer so that it can help more comminuty members who face with the same problem? Regards, Mike YinMike Yin TechNet Community Support
August 12th, 2012 7:58am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics