how to get the result set via multiple tables in stored procedure

Question

In SSRS, i got only the result set of first table, and not the second table @ABC, is there any other way to get the values of X_Q, Y_Q and Z_Q

IF OBJECT_ID('PrGetDetails') IS NOT NULL
BEGIN
DROP PROCEDURE PrGetDetails
END
GO 
CREATE PROCEDURE PrGetDetails
    AS
    BEGIN

    SET NOCOUNT ON;


    DECLARE @Details TABLE(SlNo INT,
    X_A DECIMAL(15,2),
    X_B DECIMAL(15,2),
    Y_A DECIMAL(15,2),
    Y_B DECIMAL(15,2),
    Z_A DECIMAL(15,2),
    Z_B DECIMAL(15,2),

    INSERT INTO @Details (SlNo,
     X_A,
    X_B,
    Y_A,
    Y_B,
    Z_A,
    Z_B)
    values (
  (1,1.00,0.00,1.00,0.00,1.00,0.00),
  (2,1.00,0.00,1.00,0.00,1.00,0.00))


  DECLARE @ABC TABLE (

    X_Q decimal(15,2),
    Y_Q decimal(15,2),
    Z_Q decimal(15,2)
     )
    insert into @ABC (
    X_Q,
    Y_Q,
    Z_Q)
    values (
    3.00,
    4.00,
    5.00)

     SELECT * FROM @Details
     select * from @ABC
    END
    GO

There are no way to get two tables from one stored procedure in SSRS. But you can split your SP to two different procedures and create two datasets in SSRS. If data in second table depends on first table, you can call first SP and get data from it. — Indian, May 27 '15 at 14:32

score 2 · Answer 1 · answered May 27 '15 at 13:04

2

SSRS supports only first table from the stored procedure's result.

answered May 27 '15 at 13:04

Exactly, but i need to call 3 values (X_Q, Y_Q and Z_Q) hence created ABC table, is there any other way to get the values to my report replacing the table – Kavr May 27 '15 at 13:08
If you want only ABC table than why querying on Details table? – May 27 '15 at 13:19
i need both the result sets – Kavr May 27 '15 at 13:23
3

you can use full join or union both table and add new column to identify row from which table and filter it at tablix level – May 27 '15 at 13:24

score 0 · Answer 2 · answered May 28 '15 at 01:28

You just make a wide table with both results, and create a RecordType field that can be filtered on in your report.

DECLARE @Details TABLE(
    RecType varchar(7)
    SlNo INT,
    X_A DECIMAL(15,2),
    X_B DECIMAL(15,2),
    Y_A DECIMAL(15,2),
    Y_B DECIMAL(15,2),
    Z_A DECIMAL(15,2),
    Z_B DECIMAL(15,2),
    X_Q decimal(15,2),
    Y_Q decimal(15,2),
    Z_Q decimal(15,2)
)

INSERT INTO @Details (
    RecType,
    SlNo,
    X_A,
    X_B,
    Y_A,
    Y_B,
    Z_A,
    Z_B)
values (
        ('Details',1,1.00,0.00,1.00,0.00,1.00,0.00),
        ('Details',2,1.00,0.00,1.00,0.00,1.00,0.00)
       )

insert into @Details (RecType
    X_Q,
    Y_Q,
    Z_Q)
    values ('ABC'
            3.00,
            4.00,
            5.00)

Any your resultset would look like enter image description here

how to get the result set via multiple tables in stored procedure

2 Answers2