SP Union SP?

SP Union SP?

Post by BenignVanill » Tue, 30 Dec 2003 22:21:34



I have a system that uses a stored procedure called from a VB application
using a series of custom classes. Each call to the SP uses different
arguments to attain the specific result set. We recently had a requirement
that essentially equates to a union of the two of the data sets.

Is there an easy way to union the results of two SP's?

BV.
www.iheartmypond.com

 
 
 

SP Union SP?

Post by Tom Morea » Tue, 30 Dec 2003 22:27:54


You can create a temp table and then run INSERT EXEC:

create table #t
(
-- whatever
)

insert #t1 exec MyProc1
insert #t1 exec MyProc2

select * from #t

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


I have a system that uses a stored procedure called from a VB application
using a series of custom classes. Each call to the SP uses different
arguments to attain the specific result set. We recently had a requirement
that essentially equates to a union of the two of the data sets.

Is there an easy way to union the results of two SP's?

BV.
www.iheartmypond.com

 
 
 

SP Union SP?

Post by oj » Tue, 30 Dec 2003 22:41:34


You could create a loopback linked server and use openquery().

e.g.
exec sp_serveroption 'myserver','data access','true'
go
select *
from openquery(myserver,'exec sp_who')x

--
-oj
http://www.rac4sql.net


Quote:> I have a system that uses a stored procedure called from a VB application
> using a series of custom classes. Each call to the SP uses different
> arguments to attain the specific result set. We recently had a requirement
> that essentially equates to a union of the two of the data sets.

> Is there an easy way to union the results of two SP's?

> BV.
> www.iheartmypond.com

 
 
 

SP Union SP?

Post by oj » Tue, 30 Dec 2003 23:00:04


oops...forgot to show the union...

e.g.
exec sp_serveroption 'myserver','data access','true'
go
select *
from openquery(myserver,'exec sp_who')x
union all
select *
from openquery(myserver,'exec sp_who')y

--
-oj
http://www.rac4sql.net

 
 
 

SP Union SP?

Post by BenignVanill » Wed, 31 Dec 2003 16:01:33



Quote:> You could create a loopback linked server and use openquery().

> e.g.
> exec sp_serveroption 'myserver','data access','true'
> go
> select *
> from openquery(myserver,'exec sp_who')x

<snip>

This looks like a great solution but I can't gurantee that this app will not
hit a transaction server first, so the loopback seems to be out. I think the
temp table is my best option now.

BV.
www.iheartmypond.com

 
 
 

1. SP:ExecContextHit,SP:CacheMiss,SP:CacheInsert

Hi All

I've been reading and thinking (always dangerous combination) and
decided I want to show the following measures in a profile trace (as
well at statement starting etc etc )

SP:ExecContextHit,SP:CacheMiss,SP:CacheInsert

But I cant see how I dod this in profiler or find a mention of
them,,foudn them in bol but thats it

Any help (been a long day ) would be great

cheers then Simon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. INFORMIX CUSTOMER SUPPORT LINE?

3. Use UNION to combine results sets from EXEC'ing sp's

4. Select and subqueries?

5. UNION Select statement in SP

6. Where can I find MS SQL Server 7.0 Desktop?

7. SP Query with UNION: not possible?

8. SR. ORACLE DBA--US/PA

9. Trouble with UNION ALL in SP

10. SP Question - Insert into table using generic insert SP

11. Calling a SP from a SP

12. Return 3 values from SP to SP

13. call remote server SP from SP