How to handle 2 tables returning from SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to handle 2 tables returning from SP

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2011-10-31 : 03:24:47

Hi All,

I am using a stored procedure.
SP returning 2 tables, i want to insert those 2 tables into 2 temporary tables.

What will be the syntax for this funtionality?

Thanks in advance..

Anuj Pratap Singh

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-31 : 03:33:21

Check following to insert value in temp table

select * into #temptable from yourtablename

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 04:21:40

you need to merge the two resultsets into single one using union all or something with a dervied field like below


CREATE PROC procname
AS
....

SELECT ....,'First Resultset' as category
FROM.....
..

UNION ALL
SELECT ....,'Second Resultset'
FROM
.....
..
GO


then do insertions like

INSERT #FirstTempTable
SELECT columns...
FROM OPENROWSET('SQLOLEDB','Data Source=Server_name;Trusted_Connection=yes;           Integrated Security=SSPI','Execute yourdb..procname')
where  Category='First resultset'


INSERT #SecondTempTable
SELECT columns...
FROM OPENROWSET('SQLOLEDB','Data Source=Server_name;Trusted_Connection=yes;           Integrated Security=SSPI','Execute yourdb..procname')
where  Category='Second resultset'

another way is to add insert statements inside procedure itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources