RETRIEVE COLUMN INFORMATION OF 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 2000 Forums

Transact-SQL (2000)

RETRIEVE COLUMN INFORMATION OF STORED PROCEDURE

Author

Topic

FHeNuS
Starting Member

3 Posts

Posted - 2005-09-02 : 11:04:51

Hy gyus, sorry if i am posting a repeated topic but i didn´t find the exact word about my problem... i here it is:

I have to Create a Temporary Table Based on a Stored Procedure´s Result Set... i mean... i want to create a temporaty table with all the fields that are returned by a stored procedure... Can i do this?

Any indications will be helpfull tks anyway... :D

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-09-02 : 11:19:13

Try this code:

-- 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, 'set fmtonly on exec sp_who') 
select * from #t
drop table #t
go

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-02 : 12:11:41

whats wrong with

INSERT INTO #tmp EXEC YourSP

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-09-02 : 13:10:36

quote:
Originally posted by DonAtWork

whats wrong with

INSERT INTO #tmp EXEC YourSP
...

you shoild create #tmp table before useing "INSERT INTO #tmp ..."

Check FHeNuS post: "... i want to create a temporaty table with all the fields that are returned by a stored procedure..."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-02 : 13:22:05

quote:

Originally posted by VladRUS.ca

Try this code:

-- 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, 'set fmtonly on exec sp_who') 
select * from #t
drop table #t
go

Nice trick, Vlad!

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-03 : 10:47:39

>>but how do i execute a procedure that its not on the master database
just qualify the sp name (<dbname>.<owner>.<spName>)

---------------------
EDIT:
hmm...that question was there a minute ago, I guess he figured it out and deleted the last post...
---------------------

Although Vlad's "trick" is cool and works, it may not be too good for high contention and concurrency. I wouldn't want a linked server added and dropped thousands of times. Also collisions are likely. one instance drops the linked server just as another process is ready to use it.

I think this is a good trick to create the table at design time, script it out just to get the code then drop the table. Then you can place the create proc code in your stored procedure.

Be One with the Optimizer
TG

FHeNuS
Starting Member

3 Posts

Posted - 2005-09-05 : 08:43:24

quote:
Originally posted by TG

>>but how do i execute a procedure that its not on the master database
just qualify the sp name (<dbname>.<owner>.<spName>)

---------------------
EDIT:
hmm...that question was there a minute ago, I guess he figured it out and deleted the last post...
---------------------

Although Vlad's "trick" is cool and works, it may not be too good for high contention and concurrency. I wouldn't want a linked server added and dropped thousands of times. Also collisions are likely. one instance drops the linked server just as another process is ready to use it.

I think this is a good trick to create the table at design time, script it out just to get the code then drop the table. Then you can place the create proc code in your stored procedure.

Be One with the Optimizer
TG

hehe... its was that... i found myself the answer and thought it was better to delete it... a dummy question it was... :p... tks anyway

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-09-05 : 09:34:39

quote:
Originally posted by TG

... I wouldn't want a linked server added and dropped thousands of times. Also collisions are likely. one instance drops the linked server just as another process is ready to use it.
...

Linked server should be created just one time - when your aplication is started:

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-06 : 10:56:01

quote:
--------------------------------------------------------------------------------
Originally posted by DonAtWork

whats wrong with

INSERT INTO #tmp EXEC YourSP
...

--------------------------------------------------------------------------------

"you shoild create #tmp table before useing "INSERT INTO #tmp ...""

Yet you do exactly the same thing?
"select * into #t from openquery(loopback, 'set fmtonly on exec sp_who') "

"Check FHeNuS post: "... i want to create a temporaty table with all the fields that are returned by a stored procedure...""

Exactly. Read his post. He does not know the table structure ahead of time. It is perfectly valid to execute the statement I gave. While it may not be best practice, it does indeed solve the problem. From reading his post, I cannot tell if he just wants the table structure of the return, or also the data. My solution gives him both. he can simply discard the data if he does not need it. Most of the time, the simple answer is the best.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-06 : 11:04:35

there is a difference...
for instance you have a sproc sp_who.
now you want to filter it based on db name.
sp_who returns for example 1000 rows.
how will you filter the returned result if you don't know the table structure to create the #tmp table in the first place??

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

Kristen
Test

22859 Posts

Posted - 2005-09-06 : 11:31:12

Don@work:

INSERT INTO #tmp EXEC YourSP

This won't work - the table #tmp must pre-exist for this to work

"Yet you do exactly the same thing?
"select * into #t from openquery(loopback, 'set fmtonly on exec sp_who') ""

Not exactly, using OPENQUERY is using him to do a SELECT * INTO - which does allow a non-pre-existing table to be used

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-07 : 13:53:19

Yes, i see that now. thought i have done it that way before, but i didnt. bad memory for me.

SELECT INTO != INSERT INTO

definately needed more coffee that day

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources