SSRS: How do I execute a stored procedure without binding it to the report?

Question

I have a SQL stored procedure (spOut) that needs to be executed when my SSRS report is first opened, but not again after that because it takes a long time to run and the data is fairly stable. The output from spOut is a table called tblOut.

Then I want to bind the report tablix to a parameter-driven view that queries tblOut (SELECT * FROM tblOut WHERE tblVar = @Pedro Antonio Rosa Mendes ). The parameter will allow the user to switch among different views of the data without re-executing the sproc.

How do I execute a sproc without binding it to the report?

Answer 1

Hi @Darrell Burns
Do you mean in the database, execute a stored procedure, then output the result of the stored procedure to another table, and then bind this table to the report? Then this process may need to modify the stored procedure.
If I misunderstood you, please tell me more information and your needs.
Best regards,
Aniya

Answer 2

Option 2 is my preference but I need a way to execute the stored procedure when the report is initially opened...

Answer 3

Hi @Darrell Burns
I think you can store the result of the stored procedure in another table and then use this table when querying. From what I've searched, there can be two ways to store the result of a stored procedure into another table.

1. Use the insert into statement. This method requires you to create a table first, and then insert the results into this table.
   For more details, you can refer to this link: how-to-get-the-output-of-a-stored-procedure-into-a-table.
2. Use the select into statement. This statement does not need to create a new table, but needs to enable Ad Hoc Distributed Queries, connect to the machine through OPENROWSET, run the stored procedure, and get the result set. Enabling Ad Hoc Distributed Queries has certain security risks, you need to close it after use.
   I did a test using method 2. AdventureWorksDW2014.dbo.SP_Product is a stored procedure that inserts its output into a new table ShipSummary. exec sp_configure 'show advanced options',1
   reconfigure
   exec sp_configure 'Ad Hoc Distributed Queries',1
   reconfigure
   --enable Ad Hoc Distributed Queries select * into ShipSummary from openrowset('sqloledb', 'server=(local);trusted_connection=yes','exec AdventureWorksDW2014.dbo.SP_Product')
   select * from ShipSummary
   --connect to the machine through OPENROWSET with windows authentication,run the stored procedure, and get the result set.
   --You can also use this statement in an SSRS query drop table ShipSummary
   --drop the table exec sp_configure 'Ad Hoc Distributed Queries',0
   reconfigure
   exec sp_configure 'show advanced options',1
   reconfigure
   --close Ad Hoc Distributed Queries

Hope this can help you.
Best regards,
Aniya

Answer 4

I appreciate your help Aniya, but I'm asking a different question. Let's try this:

How do I execute a stored procedure from SSRS without making it a report dataset?

Answer 5

Yes, udp_ScheduledShipments truncates and fills the ShipSummary table based on a start-date parameter. The tablix is bound to a dataset that queries ShipSummary with a variety of other parameters. udp_ScheduledShipments is executed in its own dataset.

My objective is to call udp_ScheduledShipments when the report is initially opened, and not again unless its date parameter is changed. In other words, I want the user to be able to change the other parameters and refresh the report without re-executing udp_ScheduledShipments each time.