Post reply

Creating Multiple Datasets from stored procedure

  • February 25, 2009 at 12:04 pm

    #201609

    Hi

    I have a stored procedure that returns multiple resultsets just as below

    Create StoredProcedure sp_MultipleDataSets

    as

    begin

    SELECT EMPID, ENAME, JOB, SAL, DEPTID FROM EMP -- first result set

    SELECT DEPTID, DNAME, LOC FROM DEPT --second result set

    end

    In BIDS, while creating a new report i configured the stored procedure for dataset. It creates the dataset ONLY with the columns returned from the first result set. It does not identify the second result set.

    How can I create datasets for both the result sets from a stored procedure like above.

    Thanks in Advance

    ItzMe

  • February 25, 2009 at 12:13 pm

    #950204

    I don't think this is possible. According to me you have to create 2 different SPs and have 2 datasets for each to get the result.

    If someone knows the solution(using 1 SP for 2 datasets) then I am also interested in the same

    -Vikas Bindra

  • February 25, 2009 at 12:51 pm

    #950233

    hi sam,

    If a stored procedure returns multiple rowsets (executes multiple SELECT statements), only the first rowset is processed by the report.

    If you need all results, consider implementing a wrapper stored procedure that merges the multiple rowsets in a temporary table and returns all rows with one SELECT statement.

  • February 25, 2009 at 12:56 pm

    #950237

    Can you explain me the method of wrapping up the stored procedure and merging multiple rowsets in a temporary table in detail....

    Thanks in Advance

    ItzMe

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply