Receive multiple result sets in stored procedure
1
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 LogicBest Logic
668
add a comment |
1
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 LogicBest 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 |
1
1
1
2
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 LogicBest 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 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 LogicBest Logic
668
edited Nov 24 '18 at 11:25
Sami
8,43331240
asked Nov 24 '18 at 11:10
Best LogicBest 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
8,43331240
asked Nov 24 '18 at 11:10
Best LogicBest Logic
668
asked Nov 24 '18 at 11:10
Best LogicBest Logic
668
asked Nov 24 '18 at 11:10
Best LogicBest Logic
668
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
"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
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
1
Possible duplicate of Retrieve data from stored procedure which has multiple result sets
– Kevin
Nov 24 '18 at 12:43
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
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
2
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.
answered Nov 24 '18 at 12:05
LarnuLarnu
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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.
answered Nov 24 '18 at 12:05
LarnuLarnu
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 |
2
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.
answered Nov 24 '18 at 12:05
LarnuLarnu
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 |
2
2
2
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.
answered Nov 24 '18 at 12:05
LarnuLarnu
16.5k41630
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.
answered Nov 24 '18 at 12:05
LarnuLarnu
16.5k41630
edited Nov 24 '18 at 13:06
answered Nov 24 '18 at 12:05
LarnuLarnu
16.5k41630
answered Nov 24 '18 at 12:05
LarnuLarnu
16.5k41630
answered Nov 24 '18 at 12:05
LarnuLarnu
16.5k41630
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
1
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
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
@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
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
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
"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