MS SQL Server Reporting Server multiple query in one dataset

Question

To have an expected result I must run these three queries for example:

insert into tb1(a,b) select 1,2 // input data for stored procedure
exec sp // execute stored procedure
select * from tb2 // output of stored procedure

and show tb2 data in report

How can it be performed in SSRS?

Kevin Hogg · Accepted Answer · 2014-03-19 13:06:24Z

1

You can enclose all three steps within a single stored procedure which is called as your main dataset e.g.:

CREATE PROCEDURE [Reporting].[GetDataForMyReport]
    @Parameter1 [NVarChar](50),
    @Parameter2 [NVarChar](200)
AS
    -- Step 1
     INSERT INTO tbl1
            (a, b)
     VALUES (@Parameter1, @Parameter2)

    -- Step 2
    EXEC sp

    -- Step 3
     SELECT * -- Should explicitly declare the fields to return
       FROM tbl2

However, you should separate the concerns by having something else prepare the data, whilst the report only displays (and formats) the data.

answered Mar 19, 2014 at 13:06

Kevin Hogg

1,78125 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS SQL Server Reporting Server multiple query in one dataset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related