Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Can a temporary table created with an execute statement survive that statement? Expand / Collapse
Author
Message
Posted Tuesday, March 11, 2008 5:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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?
Post #467287
Posted Tuesday, March 11, 2008 5:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
Points: 480, Visits: 467
OK, Jeff, but that was not what I was asking.
Post #467289
Posted Tuesday, March 11, 2008 5:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467294
Posted Tuesday, March 11, 2008 5:53 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:44 AM
Points: 798, Visits: 998
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'
Post #467295
Posted Tuesday, March 11, 2008 5:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467296
Posted Tuesday, March 11, 2008 6:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 30, 2010 11:27 AM
Points: 435, Visits: 1,403
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!!
Post #467301
Posted Tuesday, March 11, 2008 6:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 4, 2013 4:22 PM
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.
Post #467308
Posted Tuesday, March 11, 2008 6:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467314
Posted Tuesday, March 11, 2008 6:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,995, Visits: 31,516
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #467316
Posted Wednesday, March 12, 2008 2:25 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, 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.......
Post #468386
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse