Access to Result sets from within Stored procedures Transact-SQL SQL Server
The short answer is: you can't do it.
From T-SQL there is no way to access multiple results of a nested stored procedure call, without changing the stored procedure as others have suggested.
To be complete, if the procedure were returning a single result, you could insert it into a temp table or table variable with the following syntax:
INSERT INTO #Table (...columns...)
EXEC MySproc ...parameters...
You can use the same syntax for a procedure that returns multiple results, but it will only process the first result, the rest will be discarded.
Retrieve data from stored procedure which has multiple result sets
It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:
Old way:
create procedure dbo.GetSomething
as
begin
select * from dbo.Person;
select * from dbo.Car;
end;
New way:
create procedure dbo.GetPeople
as
begin
select * from dbo.Person;
end;
create procedure dbo.GetCars
as
begin
select * from dbo.Car;
end;
-- This gives the same result as before
create procedure dbo.GetSomething
as
begin
exec dbo.GetPeople;
exec dbo.GetCars;
end;
Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.
TSQL openquery with different result sets
You may be able to use a TRY/CATCH, TRY one ResultSet, CATCH the other;
https://msdn.microsoft.com/en-us/library/ms179296.aspx
How to SELECT FROM stored procedure
You can use a User-defined function or a view instead of a procedure.
A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT
statement.
Best way/practices to generate multiple result sets in stored procedure to different tabs in Excel
In the stored procedure give same column names to all the select statements and use UNION
between them and then have an additional column to each select statement to identify which table the data is coming from, ideally use table name.
Then call the stored procedure into a main tab once and then reference that data into other tabs and filter them.
Execute Stored Procedure with multiple result sets
Solution Overview
I made 2 Experiments on that issue, the first experiments showed that in case of stored procedures with no parameters, nothing changed in SQL Server 2016 and SSIS 2016, the first Result Set is returned and others are ignored.
The second experiment showed that when using parameters, this will throw an exception, so you have to define metadata using WITH RESULT SETS
option, then remove this option.
Detailed Solution
Experiment 1
The following experiment are made using SQL Server 2016 and Visual Studio 2015 with SSDT 2016
First i created this stored procedure
CREATE PROCEDURE sp_Test
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 10 PersonType,NameStyle,Title
FROM [AdventureWorks2016CTP3].[Person].[Person]
SELECT TOP 10 PersonType,Firstname,Lastname
FROM [AdventureWorks2016CTP3].[Person].[Person_json]
END
GO- Then i added a Data flow task to SSIS package
- Added an OLEDB Source, Recordset destination
- In OLEDB source i select the Data access mode to
SQL command
an use the following commnad
EXEC sp_Test
- When clicking on Columns Tab it shows the first ResultSet structure
- And we i executed the package it runs succesfully
Experiment 2
I changed the stored procedures to the following:
ALTER PROCEDURE [dbo].[sp_Test]
@param1 varchar(10),
@param2 varchar(10),
@param3 varchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 10 PersonType,NameStyle,Title ,@param2 as 'Param'
FROM [AdventureWorks2016CTP3].[Person].[Person]
SELECT TOP 10 PersonType,Firstname,Lastname,@param3 as 'Param'
FROM [AdventureWorks2016CTP3].[Person].[Person_json]
END
And i used the following SQL Command in the OLEDB Source:
EXEC sp_Test ?,?,?
WITH RESULT SETS (
(
PersonType NVarchar(10),
NameStyle NVarchar(10),
Title NVarchar(10),
Param Varchar(10)
)
)
And i mapped the parameters correctly.
When running the package it throws the following exception.
[OLE DB Source 2] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), and the statement tried to send more result sets than this.".
After that i tried to remove the With RESULT SETS
option, so the command is :
EXEC sp_Test ?,?,?
I tried to execute the package again, so it is executed with no errors.
Conclusion
Try to use the WITH RESULT SETs
option to define the OLEDB Source metadata
, after that the metadata is defined, just remove this option and run the package, so it will just take the first Result Set succesfully.
Related Topics
SQL Selecting Rows by Most Recent Date with Two Unique Columns
How to Reorder Rows in SQL Database
How to Pass Parameters to a View in SQL
SQL Server Equivalent to MySQL Enum Data Type
Bulk/Batch Update/Upsert in Postgresql
Optimized SQL for Tree Structures
How to Make a Composite Key with SQL Server Management Studio
Difference Between Subquery and Correlated Subquery
Paging SQL Server 2005 Results
MySQL - How Many Columns Is Too Many
Select a Column in SQL Not in Group By
Double Colon '::' Notation in SQL
Saving Changes After Table Edit in SQL Server Management Studio
Group by Date Only on a Datetime Column
Change Postgresql Columns Used in Views
What Datatype to Use When Storing Latitude and Longitude Data in SQL Databases