CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Abbi
Abbi

Reputation: 597

accessing multiple resultset in sql server

I am working with sql server 2005 and working with an old stored proc which was written by someone else. I just want to call that stored proc to get data and not modify it.

The problem is that the stored proc is returning multiple result set with exactly same fields. Just the data is bit different. So when that stored proc is being called in the front end it is being used to populate two diffrent data tables which is fine.

But now I need to work with the combined results in excel and so dont have the advantages of multiple data tables.

Basically I want create a new stored proc which returns the union of the two results just by calling the existing storedproc. I dont want to create another copy of stored proc as I will have to keep tab everytime the other stored proc is changed i will have to change mine.

Is there a way to access the second result set in sql server itself.

Thanks,

--Abhi

Upvotes: 1

Views: 408

Answers (1)

deeg
deeg

Reputation: 528

Create a proxy procedure that throws both sets into a temp table, and select from that.

Here's a test example...

/*Create first proc that returns two data sets*/
IF OBJECT_ID('ReturningTwoDataSets') IS NOT NULL
BEGIN
    DROP PROCEDURE ReturningTwoDataSets
END
GO

CREATE PROCEDURE dbo.ReturningTwoDataSets
AS
BEGIN
    SET NOCOUNT ON

    SELECT  '1' AS [col1]
            ,'2' AS [col2]
            ,'3' AS [col3]

    SELECT '4' AS [col1]
            ,'5' AS [col2]
            ,'6'  AS [col3]

END
GO



/*  
    Create new proc that combines both data sets 
    into a temp table and returns a single dataset
*/
IF OBJECT_ID('ReturningOneDataSet') IS NOT NULL
BEGIN
    DROP PROCEDURE ReturningOneDataSet
END
GO

CREATE PROCEDURE dbo.ReturningOneDataSet
AS
BEGIN
SET NOCOUNT ON

    IF OBJECT_ID('TempDB..#OneDataSet') IS NOT NULL
    BEGIN
        DROP TABLE #OneDataSet
    END

    CREATE TABLE #OneDataSet
    (
        [col1]  VARCHAR(100)
        ,[col2] VARCHAR(100)
        ,[col3] VARCHAR(100)
    )

    INSERT INTO #OneDataSet
    (
        col1
        ,col2
        ,col3
    )
    EXEC ReturningTwoDataSets


    SELECT * FROM #OneDataSet

END
GO

/*Execute the old proc*/
EXEC ReturningTwoDataSets 
/*Execute the new proc*/
EXEC ReturningOneDataSet

Upvotes: 3

Related Questions