Using EXEC functions and temp tables

Question

Using EXEC functions and temp tables

rourrourlogan

SSC Enthusiast

Points: 199
More actions
July 14, 2015 at 4:28 pm

#305867

Hello,
here's an example of what I am trying to do.
--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
Thanks,

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

rourrourlogan (7/14/2015)
Hello,
here's an example of what I am trying to do.
--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
Thanks,

Yeah, it just doesn't work that way. I believe I remember seeing something like this before:

CREATE TABLE #temptable (

FLD1 int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

FLD2 varchar(20) NOT NULL,

FLD3 int

)

INSERT INTO #temptable (FLD2, FLD3)

EXEC database.schema.procname parameters

And as long as the procedure being executed returns a recordset that contains just FLD2 and FLD3, I think that works.

Someone will have to let me know if I have that incorrect, as I haven't needed to do this in a rather long time, and the

memory I have on this is a tad vague.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Let me try to make some points clear.

- What you're referring as Database, might actually be a Schema.

- If you run your code as it is now (without comments), the SELECT statement will be part of the procedure, because the SELECT is part of the same batch. No, BEGIN and END won't define the begin and end of the procedure.

- The procedure is called before it's created, so it will fail.

- A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table. (Directly from BOL)

Would these points answer your questions? Do you have any more questions?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

In addition, since this is a simplification of the actual problem, it's possible (though impossible to tell for sure) that a temp table isn't necessary at all. Are you able to describe the actual problem?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rourrourlogan SSC Enthusiast Points: 199 More actions · Answer 4

I don't have a problem necessarily I just want to clean up my code so I don't have these massive querys setting up temp tables at the beginning of my script. I just want to have a nice clean one line statement that will put my views into a temp table. It makes it easier for maintenance and easier on the eyes :).

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Hehehe. That's the kind of thing that, when I'm optimising procedures, I tend to take out.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110651 More actions · Answer 6

rourrourlogan (7/15/2015)
I don't have a problem necessarily I just want to clean up my code so I don't have these massive querys setting up temp tables at the beginning of my script. I just want to have a nice clean one line statement that will put my views into a temp table. It makes it easier for maintenance and easier on the eyes :).

Okay, but I see you've used the word "maintenance", which suggests "permanence", which suggests that maybe a temp table shouldn't be temporary... The point being, that when you aren't willing to do the harder thing (longer script to "get it right"), what else are you avoiding that you shouldn't be? It just leads to more of the same kind of sloppy behaviors that tend to produce more problems rather than less. Easy on the eyes doesn't necessarily translate to less maintenance.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

rourrourlogan (7/14/2015)
Hello,
here's an example of what I am trying to do.
--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
Thanks,

As mentioned, the final select above will be included in the stored procedure unless you put a GO between the end and the select.

In addition, the select will fail as the temporary table created with the SELECT INTO in the stored procedure will be dropped by SQL Server when the stored procedure execution finishes.