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.
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.
Stored Procedure return multiple result sets
You pretty much just select two result sets
SELECT * FROM @myTable1
SELECT * FROM @myTable2
However, some tools will hide some results (e.g. pgAdmin will only show the last) and some tools have some sort of requirement to get to the next result set (e.g. .NET's IDataReader's will not allow you to Read()
from the second resultset until you call NextResult()
).
Edit:
An alternative in this case, since the types of the two results match, is to combine them into a single resultset:
SELECT field0, field1 from @myTable1
UNION
SELECT field0, field3 from @myTable2
You can also choose between UNION ALL
or UNION DISTINCT
(the default) where the latter will only send rows that aren't repeats.
How to save the last result set of SP which is returning multiple result sets into a temp table in SSIS?
From the docs:
If the Execute SQL task uses the Full result set result set and the
query returns multiple rowsets, the task returns only the first
rowset. If this rowset generates an error, the task reports the error.
If other rowsets generate errors, the task does not report them.
So, SSIS Execute SQL Task cannot access multiple result sets from a single proc. Only the first can be accessed.
Store the multiple result sets from stored procedures to different tables
You can use the system variable @@ROWCOUNT to get the number of rows affected by your query.
The value of @@ROWCOUNT is updated with the number of rows affected by your last statement ( select / insert / update / delete ). so after the execution of your stored procedure, you can check the value in @@ROWCOUNT to get the number of rows returned by the procedure. Like this
declare @c int
exec OrderDetails
select @c =@@Rowcount
select @c
Here the no of rows returned by the procedure OrderDetails will be stored in a user-defined variable @c and you can use the same for any other operations.
Please refer this Link for more details on @@ROWCOUNT
How to return multiple resultset from a stored procedure using cte?
As other two ops has already explain how CTE works I am not going to go over it again what you can do is to insert the records returned from cte into temp table and select from it multiple times,
something like .....
ALTER PROCEDURE abc(
@startDate date,
@endDate date
)
AS
BEGIN
;WITH allOrders AS(
SELECT * FROM Orders
)
SELECT * INTO #temp FROM allOrders;
SELECT * FROM #temp;
SELECT * FROM #temp;
END
Can Postgres 13 stored procedures return multiple result sets similar to SQL Server?
If you mean to return a result set by executing a plain query - no, such syntax is not supported in PL/pgSQL.
If you mean to return a result set by using a cursor - yes, PostgreSQL stored procedures can return cursors too.
You can define multiple cursor output parameters: https://www.postgresql.org/docs/13/plpgsql-cursors.html
Related Topics
Xml Output Is Truncated in SQL
How to See Progress of Running SQL Stored Procedures
What Is the Ms SQL Server Capability Similar to the MySQL Field() Function
Is Order in a Subquery Guaranteed to Be Preserved
Combining Results of Two Select Statements
Flattening Intersecting Timespans
How to Tell If I Have Uncommitted Work in an Oracle Transaction
JSONb Query with Nested Objects in an Array
Looking for a SQL Transaction Log File Viewer
How to Take Sum of Column with Same Id in SQL
SQL Server If Not Exists Usage
Clean Way to Use Postgresql Window Functions in Django Orm
How to Order by Parent Then Child
Why Even Use *Db.Exec() or Prepared Statements in Golang
How to Convert Int to Date in SQL Server 2008