Click here to monitor SSC
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
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 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-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 467
OK, Jeff, but that was not what I was asking.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45030 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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

Group: General Forum Members
Points: 801 Visits: 1017
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45030 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jim Russell-390299
Jim Russell-390299
SSC-Addicted
SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)SSC-Addicted (449 reputation)

Group: General Forum Members
Points: 449 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-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45030 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45030 Visits: 39893
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Bob Fazio
Bob Fazio
SSC Eights!
SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)SSC Eights! (968 reputation)

Group: General Forum Members
Points: 968 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