Stored procedure into tmp table

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)

Stored procedure into tmp table

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-21 : 18:21:09

I am writing the following stored procedure


Alter Proc Sproc_Employer_Retrieve_GetEmployeesByEmployer
@EmployerID int
as


exec SpProductionDB.dbo.Sproc_Employer_GetEmployees @EmployerID
into PIUMSSite.#Tmp


Select *, coalesce(b.ChangedStatusID,0) as Pending
from
PIUMSSite.#Tmp a
Left Join
PIUMSSite.T_Changed_Employee b
on a.EmployeeID = b.EmployeeID
where 
b.ChangeStatusID = 1

Does anyone know how to achieve a select Into on a stored procedure? Is there any way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-21 : 18:24:24

Temp table are local to current context.
You can use global temp tables, but they need to be prefixed with double #.

##Temp

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-21 : 18:27:31

Thanks peso. I changed the initial post to clarify. I am trying to achieve more of a "select Into" with the stored procedure where the table is created based off the results. I don't think theres anyway to do this without using a Openrowset or something.. Am I correct?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 18:33:48

CREATE TABLE ...

INSERT INTO ...
EXEC spName ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-21 : 18:36:16

Thanks, but I am looking to "SELECT INTO" or "Exec Into" without needing to declare the table and code all the column names from a stored procedure. Reason being is I need to do this on about 20 or so stored procedures and creating a tmp table by hard-coding each column is going to take a long time!

Thanks again for the help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-21 : 18:43:28

I guess I asked this same exact question a while back. Here's the answer

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85347&SearchTerms=exec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources