SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can a temporary table created with an execute statement survive that statement?


Can a temporary table created with an execute statement survive that statement?

Author
Message
Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2221 Visits: 1403
Great suggestion Jeff!
I don't suppose I can prefix the temp procedure name with a '#', so my "main" will need to drop it when done?
Kent Waldrop
Kent Waldrop
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 467
OK, Jeff, but that was not what I was asking.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212289 Visits: 41977
Jim Russell (3/11/2008)
Great suggestion Jeff!
I don't suppose I can prefix the temp procedure name with a '#', so my "main" will need to drop it when done?


Temporary stored procedures begin with a "#" and are treated the same way as Temp Tables so far as scope goes...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dragos_sv
dragos_sv
SSC Eights!
SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)SSC Eights! (937 reputation)

Group: General Forum Members
Points: 937 Visits: 1029
Jim Russell (3/11/2008)
Great suggestion Jeff!
I don't suppose I can prefix the temp procedure name with a '#', so my "main" will need to drop it when done?


yes you can
check BOL for 'temporary stored procedures'
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212289 Visits: 41977
kent waldrop (3/11/2008)
OK, Jeff, but that was not what I was asking.


Thought I'd answered your question, Kent... what were you actually asking?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jim Russell-390299
Jim Russell-390299
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2221 Visits: 1403
There was a lot of discussion re temp tables in this exchange:

http://www.sqlservercentral.com/Forums/Topic439174-8-1.aspx

where I got a lot of good answers from Jeff and others.


and...re #procedures, I would have never guessed. Wonderful!!
Kent Waldrop
Kent Waldrop
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 467
After re-reading the exchange I think my question was wrong because I was focusing on the wrong part of the question. The orignal question, "... Is it possible for a temporary table to survive that execute statement? ..." Which was answered with a "yes, but ... "

Really, my question was NOT the fact that the temp table disappeared -- I expect that behavior -- but mis-directed at the "Yes" portion of the "Yes, but". My question was what are the circumstances in which you can use "exec ('create ...')" to create a temp table and expect that the temp table would persist beyond the exec statement; I hadn't observed that before. So I think the aim of the question was off to begin with. Sorry for confusing things.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212289 Visits: 41977
Heh... yeah... they break the hell out of the 4k barrier of sp_ExecuteSQL and will return return codes just like the real ones. Neat thing is, if you need it more that once, it's there so long as it's within scope.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212289 Visits: 41977
kent waldrop (3/11/2008)
After re-reading the exchange I think my question was wrong because I was focusing on the wrong part of the question. The orignal question, "... Is it possible for a temporary table to survive that execute statement? ..." Which was answered with a "yes, but ... "

Really, my question was NOT the fact that the temp table disappeared -- I expect that behavior -- but mis-directed at the "Yes" portion of the "Yes, but". My question was what are the circumstances in which you can use "exec ('create ...')" to create a temp table and expect that the temp table would persist beyond the exec statement; I hadn't observed that before. So I think the aim of the question was off to begin with. Sorry for confusing things.


So, for the scope of what you want to do, the answer would be "No".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Bob Fazio
Bob Fazio
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2990 Visits: 683
Forgive this for being thrown together in about 10 min.

I suspect this is what the OP is trying to do:

ALTER PROCEDURE bobTest
@srcTableID INT,
@destTableName sysname = NULL,
@execSQL VARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Cntr INT, @theSQL VARCHAR(MAX)

SELECT @Cntr = 1

SELECT column_ID,NAME
INTO #wrkTable
FROM tempdb.sys.columns
WHERE tempdb.sys.columns.[object_id] = @srcTableID
ORDER BY Column_ID

WHILE EXISTS(SELECT 1 FROM #wrkTable WHERE column_ID = @Cntr)
BEGIN
SELECT @theSQL = 'alter table ' + @destTableName + ' add ' + QUOTENAME(NAME) + ' SQL_VARIANT '
FROM [#wrkTable] WHERE column_ID = @Cntr

PRINT @theSQL
EXEC (@theSQL)
SELECT @Cntr = @Cntr + 1
END

SELECT @theSQL = 'alter table ' + @destTableName + ' drop column a'
EXEC(@theSQL)

SELECT @theSQL = 'INSERT INTO ' + @destTableName + ' ' + @ExecSQL
EXEC(@theSQL)
END
GO

IF OBJECT_ID('tempdb.dbo.#theStart') IS NOT NULL
DROP TABLE [#theStart]
CREATE TABLE #theStart (a INT)

IF OBJECT_ID('tempdb.dbo.#myTest') IS NOT NULL
DROP TABLE #myTest

SELECT *
INTO #myTest
FROM sys.tables
DECLARE @destTableID INT

SELECT @destTableID = OBJECT_ID('tempdb.dbo.#myTest')

EXEC bobTest @destTableID,'#theStart','select * from sys.tables'

SELECT * FROM #theStart



Now realize that I have just created a HUGE HUGE SQL INJECTION opportunity. I don't suggest you use this code.......

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search