sql server programming: union operator with sp's -- sql server programming

RE: union operator with sp's

Ishan Bhalla
9/21/2004 11:23:03 PM

Thanks guys for the exmples and hints

[quoted text, click to view]

Re: union operator with sp's

David Portas
9/22/2004 6:58:49 AM

You can INSERT the results of an SP into a table:

INSERT INTO SomeTable (au_lname)
EXEC sp1

INSERT INTO SomeTable (au_lname)
EXEC sp2

SELECT * FROM SomeTable

but you can't query the results of an SP directly in a SELECT statement.

See also: http://www.sommarskog.se/share_data.html

--
David Portas
SQL Server MVP
--

Re: union operator with sp's

Uri Dimant
9/22/2004 9:05:39 AM

Ishan
CREATE PROC spMain
AS
CREATE TABLE #T1
(
col1 INT
)
CREATE TABLE #T2
(
col2 INT
)

INSERT INTO #T1 EXEC SP1
GO
INSERT INTO #T2 EXEC SP2

SELECT col1 FROM #T1
UNION
SELECT col1 FROM #T2

[quoted text, click to view]

Re: union operator with sp's

Vishal Parkar
9/22/2004 11:39:41 AM

union all clause works only with SELECT statements. if possible try to merge
select statements in one stored procedure.
you can try following workaround as well.

ex:
--merging two row sets from sp_who stored procedure by temporarily sending
output to temp tables.

use master
go
EXEC sp_serveroption '<server_name>', 'data access' , 'true'
go
select * into #t
from openquery (<server_name>, 'exec sp_who')
go
select * into #t1
from openquery (<server_name>, 'exec sp_who')
go

select * from #t
union all
select * from #t1

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.