Click here to Skip to main content
15,919,358 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hi

I have one Sp "PROCS.abc" which Give two different Result Set , we need to save them in two Different tables by calling another SP example PROCS.abcd.

What I have tried:

Probably i not tried
I have one Sp "PROCS.abc" which Give two different Result Set , we need to save them in two Different tables by calling another SP example PROCS.abcd.
Posted
Updated 27-May-16 6:17am
Comments
W Balboos, GHB 27-May-16 8:31am    
"WE" - sounds like your asking us to do your homework. You're supposed to figure that out and learn something in the process.

1 solution

You can't access the separate result sets in T-SQL.

You can do it in code e.g. Return the results to a .NET DataSet and you will be able to access the tables. Example How to: Execute Stored Procedures Returning Multiple Result Sets[^]

If both result sets have the same schema then you can save all of the output into a temporary file and then split the data out based on whatever criteria you want Example:
SQL
CREATE PROCEDURE [dbo].[sp_TwoResults]
AS
 
BEGIN
	SELECT 'table', TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
	SELECT 'column', COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
END
GO

CREATE TABLE #temp(ObjectType varchar(6), ObjectName Varchar(50))
INSERT INTO #temp
EXEC sp_TwoResults
select * from #temp


Better would be to alter the stored procedure to put the outputs directly into the target tables or have two stored procedures - Stored procedures and multiple result sets in T-SQL[^]
 
Share this answer
 
Comments
Kannan 261190 30-May-16 2:28am    
Thank You , but we have Different Schema Situation and We cannot use Dot net Code .
CHill60 30-May-16 5:59am    
In that case you will need to either populate the tables from within the SP itself, or have two separate stored procedures. If you post the code from your SP we might be able to advise further

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900