Receive multiple result sets in stored procedure

I have a procedure Sp1:

Begin

    select product_id, product_name 

    from product



    select dept_id, dept_name 

    from department

end

My procedure returns two result sets, now I call to this procedure in another procedure using:

exec SP1

How can I access the results of SP1 in this other procedure?

edited Nov 24 '18 at 11:25

Sami

8,43331240

asked Nov 24 '18 at 11:10

Best Logic

668

"How i stored result of this SP1 in another procedure" What that mean? Can't you use a #TempTable? or even better a Function?

– Sami
Nov 24 '18 at 11:16

As far as I know, you can only capture the last "result set" from the stored procedure. As far as I'm concerned, that's a total hack anyway, and you should look into user defined functions or output variables to return values.

– Gordon Linoff
Nov 24 '18 at 12:04

1

Possible duplicate of Retrieve data from stored procedure which has multiple result sets

– Kevin
Nov 24 '18 at 12:43

This is an XY Problem. What are you actually trying to accomplish with the results of those queries?

– Eric Brandt
Nov 25 '18 at 9:46

add a comment |

I have a procedure Sp1:

Begin

    select product_id, product_name 

    from product



    select dept_id, dept_name 

    from department

end

My procedure returns two result sets, now I call to this procedure in another procedure using:

exec SP1

How can I access the results of SP1 in this other procedure?

edited Nov 24 '18 at 11:25

Sami

8,43331240

asked Nov 24 '18 at 11:10

Best Logic

668

"How i stored result of this SP1 in another procedure" What that mean? Can't you use a #TempTable? or even better a Function?

– Sami
Nov 24 '18 at 11:16

As far as I know, you can only capture the last "result set" from the stored procedure. As far as I'm concerned, that's a total hack anyway, and you should look into user defined functions or output variables to return values.

– Gordon Linoff
Nov 24 '18 at 12:04

1

Possible duplicate of Retrieve data from stored procedure which has multiple result sets

– Kevin
Nov 24 '18 at 12:43

This is an XY Problem. What are you actually trying to accomplish with the results of those queries?

– Eric Brandt
Nov 25 '18 at 9:46

add a comment |

I have a procedure Sp1:

Begin

    select product_id, product_name 

    from product



    select dept_id, dept_name 

    from department

end

My procedure returns two result sets, now I call to this procedure in another procedure using:

exec SP1

How can I access the results of SP1 in this other procedure?

edited Nov 24 '18 at 11:25

Sami

8,43331240

asked Nov 24 '18 at 11:10

Best Logic

668

I have a procedure Sp1:

Begin

    select product_id, product_name 

    from product



    select dept_id, dept_name 

    from department

end

My procedure returns two result sets, now I call to this procedure in another procedure using:

exec SP1

How can I access the results of SP1 in this other procedure?

sql sql-server database sql-server-2008

edited Nov 24 '18 at 11:25

Sami

8,43331240

asked Nov 24 '18 at 11:10

Best Logic

668

edited Nov 24 '18 at 11:25

Sami

8,43331240

asked Nov 24 '18 at 11:10

Best Logic

668

edited Nov 24 '18 at 11:25

Sami

8,43331240

edited Nov 24 '18 at 11:25

Sami

8,43331240

edited Nov 24 '18 at 11:25

Sami

8,43331240

asked Nov 24 '18 at 11:10

Best Logic

668

asked Nov 24 '18 at 11:10

Best Logic

668

asked Nov 24 '18 at 11:10

Best Logic

668

"How i stored result of this SP1 in another procedure" What that mean? Can't you use a #TempTable? or even better a Function?

– Sami
Nov 24 '18 at 11:16

As far as I know, you can only capture the last "result set" from the stored procedure. As far as I'm concerned, that's a total hack anyway, and you should look into user defined functions or output variables to return values.

– Gordon Linoff
Nov 24 '18 at 12:04

1

Possible duplicate of Retrieve data from stored procedure which has multiple result sets

– Kevin
Nov 24 '18 at 12:43

This is an XY Problem. What are you actually trying to accomplish with the results of those queries?

– Eric Brandt
Nov 25 '18 at 9:46

add a comment |

"How i stored result of this SP1 in another procedure" What that mean? Can't you use a #TempTable? or even better a Function?

– Sami
Nov 24 '18 at 11:16

As far as I know, you can only capture the last "result set" from the stored procedure. As far as I'm concerned, that's a total hack anyway, and you should look into user defined functions or output variables to return values.

– Gordon Linoff
Nov 24 '18 at 12:04

1

Possible duplicate of Retrieve data from stored procedure which has multiple result sets

– Kevin
Nov 24 '18 at 12:43

This is an XY Problem. What are you actually trying to accomplish with the results of those queries?

– Eric Brandt
Nov 25 '18 at 9:46

"How i stored result of this SP1 in another procedure" What that mean? Can't you use a #TempTable? or even better a Function?

– Sami
Nov 24 '18 at 11:16

As far as I know, you can only capture the last "result set" from the stored procedure. As far as I'm concerned, that's a total hack anyway, and you should look into user defined functions or output variables to return values.

– Gordon Linoff
Nov 24 '18 at 12:04

Possible duplicate of Retrieve data from stored procedure which has multiple result sets

– Kevin
Nov 24 '18 at 12:43

This is an XY Problem. What are you actually trying to accomplish with the results of those queries?

– Eric Brandt
Nov 25 '18 at 9:46

add a comment |

1 Answer
1

active

oldest

votes

You can get the results from an SP into a table by using the INSERT INTO..EXEC syntax. I don't advise it, however, as it relies on all datasets being returned from the SP to have the same definition:

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt'),

                (2,'Jeans'),

                (3,'Spotlight')) V(ProductID,ProductName);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

As soon as you throw in a dataset that has a different definition (for example, different number of columns, or perhaps a value that can't be implicitly cast (i.e. 'abc' to an int) it'll fail. For example:

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt',1),

                (2,'Jeans',1),

                (3,'Spotlight',2)) V(ProductID,ProductName,DeptID);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



--fails

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable;

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15),OtherID int);



--fails   

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

You should really be using multiple SP's and handling the data that way.

edited Nov 24 '18 at 13:06

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

1

The first comment in the first script says fails but I think that should be succeeds.

– Dan Guzman
Nov 24 '18 at 13:04

@DanGuzman Neither, I edited the wrong script. >_< Will fix

– Larnu
Nov 24 '18 at 13:05

I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation.

– Best Logic
Nov 27 '18 at 5:27

You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though.

– Larnu
Nov 27 '18 at 8:59

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53457527%2freceive-multiple-result-sets-in-stored-procedure%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt'),

                (2,'Jeans'),

                (3,'Spotlight')) V(ProductID,ProductName);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt',1),

                (2,'Jeans',1),

                (3,'Spotlight',2)) V(ProductID,ProductName,DeptID);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



--fails

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable;

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15),OtherID int);



--fails   

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

You should really be using multiple SP's and handling the data that way.

edited Nov 24 '18 at 13:06

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

1

The first comment in the first script says fails but I think that should be succeeds.

– Dan Guzman
Nov 24 '18 at 13:04

@DanGuzman Neither, I edited the wrong script. >_< Will fix

– Larnu
Nov 24 '18 at 13:05

I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation.

– Best Logic
Nov 27 '18 at 5:27

You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though.

– Larnu
Nov 27 '18 at 8:59

add a comment |

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt'),

                (2,'Jeans'),

                (3,'Spotlight')) V(ProductID,ProductName);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt',1),

                (2,'Jeans',1),

                (3,'Spotlight',2)) V(ProductID,ProductName,DeptID);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



--fails

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable;

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15),OtherID int);



--fails   

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

You should really be using multiple SP's and handling the data that way.

edited Nov 24 '18 at 13:06

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

1

The first comment in the first script says fails but I think that should be succeeds.

– Dan Guzman
Nov 24 '18 at 13:04

@DanGuzman Neither, I edited the wrong script. >_< Will fix

– Larnu
Nov 24 '18 at 13:05

I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation.

– Best Logic
Nov 27 '18 at 5:27

You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though.

– Larnu
Nov 27 '18 at 8:59

add a comment |

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt'),

                (2,'Jeans'),

                (3,'Spotlight')) V(ProductID,ProductName);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt',1),

                (2,'Jeans',1),

                (3,'Spotlight',2)) V(ProductID,ProductName,DeptID);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



--fails

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable;

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15),OtherID int);



--fails   

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

You should really be using multiple SP's and handling the data that way.

edited Nov 24 '18 at 13:06

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt'),

                (2,'Jeans'),

                (3,'Spotlight')) V(ProductID,ProductName);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

USE Sandbox;

GO



CREATE PROC TestProc1 AS



    SELECT *

    FROM (VALUES(1,'T-Shirt',1),

                (2,'Jeans',1),

                (3,'Spotlight',2)) V(ProductID,ProductName,DeptID);



    SELECT *

    FROM (VALUES(1,'Clothing'),

                (2,'Lighting')) V(DeptID, DepartmentName);

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15));



--fails

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable;

GO



CREATE TABLE #TempTable (ID int, [Name] varchar(15),OtherID int);



--fails   

INSERT INTO #TempTable

EXEC TestProc1;



SELECT *

FROM #TempTable;



GO



DROP TABLE #TempTable

DROP PROC TestProc1;

You should really be using multiple SP's and handling the data that way.

edited Nov 24 '18 at 13:06

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

edited Nov 24 '18 at 13:06

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

answered Nov 24 '18 at 12:05

Larnu

16.5k41630

1

The first comment in the first script says fails but I think that should be succeeds.

– Dan Guzman
Nov 24 '18 at 13:04

@DanGuzman Neither, I edited the wrong script. >_< Will fix

– Larnu
Nov 24 '18 at 13:05

I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation.

– Best Logic
Nov 27 '18 at 5:27

You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though.

– Larnu
Nov 27 '18 at 8:59

add a comment |

1

The first comment in the first script says fails but I think that should be succeeds.

– Dan Guzman
Nov 24 '18 at 13:04

@DanGuzman Neither, I edited the wrong script. >_< Will fix

– Larnu
Nov 24 '18 at 13:05

I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation.

– Best Logic
Nov 27 '18 at 5:27

You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though.

– Larnu
Nov 27 '18 at 8:59

The first comment in the first script says fails but I think that should be succeeds.

– Dan Guzman
Nov 24 '18 at 13:04

@DanGuzman Neither, I edited the wrong script. >_< Will fix

– Larnu
Nov 24 '18 at 13:05

I want this multiple result set in another stored procedure not in controller. This two result set come after lot of calculation.

– Best Logic
Nov 27 '18 at 5:27

You're statement be very vague. You can't pass datasets between stored proecures. You could (again) use a temporary table though.

– Larnu
Nov 27 '18 at 8:59

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Btukfyl