Create temp table from Stored Procedure

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 2005 Forums

Transact-SQL (2005)

Create temp table from Stored Procedure

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-11 : 04:47:04

Hi,

I'm in the middle of writing a stored procedure, and I've come across another SP in the database that selects a set of data which I need as part of my current SP. So obviously I'd like to use this old SP as part of the code in my new one.

The trouble is that all I need from the set of data are the ID fields, whereas the older SP returns a large range of fields. I don't really want to have to go to the hassle of creating a temp table with 20-30 fields when I only need one.

So the question is, is there any way in which I can run the old SP to get just the data I need? Is there some way to get it to "auto-build" the temp table in the same way as SELECT INTO for example? If I create a temp table of just the ID field, can I run the SP and get just the ID fields out? Or any other solution?

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 04:50:42

I dont think you can do a customised select of data from existing SP without altering it. The only thing you can do is create a temp table with exact structure as returned result set and then take your interested fields alone from temp table.
Alternatevely, if you can alter other SP, you can introduce a new optional parameter with default value and based on its value set you can make a customised selection.

mattt
Posting Yak Master

194 Posts

Posted - 2008-02-11 : 05:30:15

OK, thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 06:00:56

quote:
Originally posted by visakh16

I dont think you can do a customised select of data from existing SP without altering it. The only thing you can do is create a temp table with exact structure as returned result set and then take your interested fields alone from temp table.
Alternatevely, if you can alter other SP, you can introduce a new optional parameter with default value and based on its value set you can make a customised selection.

First, create a "loopback" linked server to reference the same server

EXEC sp_addlinkedserver 'loopback', '', 'SQLNCLI', @@SERVERNAME

EXEC sp_addlinkedserver 'loopback', '', SQLOLEDB', @@SERVERNAME

Then you can JOIN a resultset from a stored procedure.

This method however has a drawback. There are more security into this.

SELECT		q1.Status,
		q2.cmd
FROM		OPENQUERY(loopback, 'EXEC sp_who') AS q1
INNER JOIN	OPENQUERY(loopback, 'EXEC sp_who') AS q2 ON q2.SPID = q1.SPID

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 06:37:35

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 06:48:07

quote:
Originally posted by madhivanan

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

This is a good method. Have never thought of this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 06:55:55

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

This is a good method. Have never thought of this

Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources