UNION of EXECs? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

UNION of EXECs?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 13:07:46

I've got to build a single recordset by combining the results of a stored proc call with different parms...

for example:

EXEC MyProc @Parm1='abc'
EXEC MyProc @Parm1='def'

Is there any way to combine these results into a single recordset? Maybe using temp tables?

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-29 : 13:32:44

quote:
Originally posted by SamC

Maybe using temp tables?

Give that man a cee-gar

CREATE TABLE #temp(collist...

INSERT INTO #temp(collist) EXEC MyProc @Parm1='abc'
INSERT INTO #temp(collist) EXEC MyProc @Parm1='def'

Put the Kayak away yet? Time to go skiing....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 13:39:54

This proc returns about 45 columns.

Bonus points for a solution that doesn't require me to enumerate the columns.

Sam

PS: I'm into off-road biking now. It's been a dry summer.

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 14:06:14

CREATE TABLE #temp(collist...

INSERT INTO #temp~~(collist)~~ EXEC MyProc @Parm1='abc'
INSERT INTO #temp~~(collist)~~ EXEC MyProc @Parm1='def'

NOW can I go skiing?

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 14:15:36

OK, OK.

A

for anyone that comes up with a solution where I don't have to enumerate the columns at all!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 14:27:49

saw this here somewhere:


-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

select * into #t from openquery(loopback, 'select * from northwind..orders where orderid < 10500') 
insert into #t select * from openquery(loopback, 'select * from northwind..orders where orderid >= 10500') 
select * from #t
drop table #t
go

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 14:45:38

Yeah, but...

INSERT INTO #t

and

SELECT * INTO #t

don't work with EXEC dbo.MyProc

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 14:53:00

no?


-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

create proc proc1
@orderID int
as
select * from northwind..orders where orderid < @orderID
go

create proc proc2
@orderID int
as
select * from northwind..orders where orderid >= @orderID
go

select * into #t from openquery(loopback, 'exec northwind..proc1 ''10500''') 
insert into #t select * from openquery(loopback, 'exec northwind..proc2 ''10500''') 
select * from #t order by orderid
drop table #t
go
drop proc proc1, proc2

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-29 : 15:05:32

quote:
Originally posted by SamC

This proc returns about 45 columns.

Bonus points for a solution that doesn't require me to enumerate the columns.

Sam

PS: I'm into off-road biking now. It's been a dry summer.

I think enumerates the wrong word here...

And so what the sprocs got 45 columns.

Go into the sproc and grab the column list...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 15:06:49

or simply do

Declare @ColumnList varchar(1000)
SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'TableName'
select @ColumnList

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-09-29 : 15:57:50

Sam, are the columns returned by the SProc "pretty much" the same as one, or maybe a couple of, tables in the DB?

if so I would use the Object Viewer in Q.A., right click the relevant table(s), do "Create table to Clipboard" and paste that in and delete the columns that don't apply.

Equally if you need a list of columns for the INSERT part then I'd do the same but use "Create Insert statement to Clipboard" - and then ditch all of it except the list of columns (the formatting is not very pretty, but!)

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 16:21:16

Sorry guys, I was sawing logs.

Lemme try this...

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 16:55:34

quote:
Originally posted by X002548

Go into the sproc and grab the column list...

How do I do this? Inspection or is there a "Create column list" option in QA?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-29 : 18:46:47

quote:
Originally posted by SamC

quote:
Originally posted by X002548

Go into the sproc and grab the column list...
How do I do this? Inspection or is there a "Create column list" option in QA?

You need a table to do this, but it's one of my favorite timesavers.
In the "Object Browser" in QA, expand the table you are interested in.
grab the "columns" folder and drag&drop it into the query pane.

Someone at microsoft did some thinking.
Now I have to try it in SQL 2005..
wait...
Yes it works.
Thank You MS!

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-29 : 19:19:30

Yeah, but this is a stored proc. The returned recordset is generated by a derived query.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-29 : 19:58:50

Yeah, but if You can just get the table structure into a table somehow.

SELECT * INTO mytable FROM OPENQUERY(SERVER_NAME, 'SET FMTONLY ON; EXEC master..sp_who')

then it's trivial to get the columns.

Spirit pointed You in the right direction, I cannot find the actual topic atm.
sommarskog has som stuff here: http://www.sommarskog.se/share_data.html , http://www.sommarskog.se/dynamic_sql.html

Aren't You done typing the 45 columns by now ???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources