return data from a 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

SQL Server Development (2000)

return data from a stored procedure

Author

Topic

deppi
Starting Member

3 Posts

Posted - 2004-10-01 : 04:30:52

Hello,i want to return in a temporary table the data from a stored procedure.But I dont want to use

INSERT INTO #TEMP
EXEC ProcedureNAME.....
because i have to create first the temporary table,but i dont know it's size.

Can i use select ..... into #TEMP .....exec ...........
to get the returned data ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-10-01 : 05:01:07

#temp tables only exist for the life of scope of the object.
##temp tables may provide a longer life to satisfy your needs.

search here for info on ## prefixed tables

deppi
Starting Member

3 Posts

Posted - 2004-10-01 : 05:19:08

YES but i have to create first the table and i don't want it,because i dont know the number of the columns.
I want to execute the procedure and select the returned rows in a table without creat it first.
e.g in a simple query

select *
into #temp
from table

But how i can select data from a stored procedure?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 05:35:33

quote:
Originally posted by deppi

Hello,i want to return in a temporary table the data from a stored procedure.But I dont want to use

INSERT INTO #TEMP
EXEC ProcedureNAME.....
because i have to create first the temporary table,but i dont know it's size.

Can i use select ..... into #TEMP .....exec ...........
to get the returned data ?

this is correct...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 06:48:19

well there's only one way i know you can do it.
at the end of your sproc put the data into a global ##temp table
that way the data will be in ##temp table when you exit the sproc.
just make sure you drop the ##temp table when you're done with it.
it doesn't get droped when finishing a batch.

hope it helps.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources