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

Darrell Burns 141 Reputation points
2022-11-24T20:06:42.1+00:00

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?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,867 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor
    2022-11-25T01:44:47.447+00:00

    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

    0 comments No comments

  2. Darrell Burns 141 Reputation points
    2022-11-25T16:40:25.967+00:00

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

    264342-ssrs-screenshot.png

    0 comments No comments

  3. AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor
    2022-11-28T06:00:46.92+00:00

    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

    264614-1.png
    Hope this can help you.
    Best regards,
    Aniya

    0 comments No comments

  4. Darrell Burns 141 Reputation points
    2022-11-28T15:25:38.147+00:00

    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?


  5. Darrell Burns 141 Reputation points
    2022-11-29T17:32:07.227+00:00

    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.

    0 comments No comments