March 11, 2008 at 6:01 am
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!!
March 11, 2008 at 6:12 am
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.
March 11, 2008 at 6:19 am
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
Change is inevitable... Change for the better is not.
March 11, 2008 at 6:24 am
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
Change is inevitable... Change for the better is not.
March 12, 2008 at 2:25 pm
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.......
March 12, 2008 at 2:29 pm
After looking at the post... srcTable and destTable are confusing to say the least. :blush: Also, obviously you will need to "Create" it before you alter it.
March 12, 2008 at 3:03 pm
Adding columns to a temp table or any table that uses ALTER is always a PITA... during run time, the new columns are perceived as NOT THERE. That means everything that follows the either the dynamic creation of a temp table or alterations of same must be followed by all dynamic SQL in order to get the proc to run. For example... this will compile, but it won't run...
USE TempDB
GO
CREATE PROCEDURE dodah
AS
CREATE TABLE #MyHead (ID INT IDENTITY(1,1))
ALTER TABLE #MyHead
ADD ColA VARCHAR(10)
SELECT ColA FROM #MyHead --This won't work
GO
EXEC dodah
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2008 at 5:33 pm
correct answer is no
I have specific problem and I solved it using dynamic temp table. Main idea is split code to two (or more) separate procedures and execute nested procedure together with create table statement
My solution looks like:
-- exec load_file 'd:\\btrg.trg',2
create procedure load_file(@fname varchar(128), @cols int)
as
declare @crea nvarchar(max), @C int
set @C=0
set @crea='create table #tmp_file('
while @C<@cols
begin
set @C=@c+1
set @crea=@crea+'col'+cast(@c as varchar(10))+' varchar(50),'
end
set @crea=substring(@crea, 0, len(@crea))+') execute run_bulkinsert @fname'
exec sp_executesql @crea, N'@fname varchar(128)', @fname
go
create procedure run_bulkinsert(@fname varchar(128))
as
exec ('bulk insert #tmp_file from '''+@fname+''' with (CODEPAGE=''OEM'', DATAFILETYPE=''char'', FIELDTERMINATOR='','')')
select * from #tmp_file
go
March 12, 2008 at 11:19 pm
Jeff Moden (3/10/2008)
Giving away a fine "secret" here, but no one's ever heard of temporary stored procedures? Build it using dynamic SQL... execute it... it's all in the same scope that way. You can make temp tables with all sorts of variable column names that way... reporting on steriods... no RBAR... no global temp table or fixed table headaches.
Does that work better than multiple Exec I was thinking of? Just curious. I can't say I've played with temp SP's...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 7:46 am
Is there any reason not to just create a table with create table tempdb..tablename...., and then explicitly drop the table when it's no longer needed? Taht table will survive until dropped or the server is rebooted. The vendor app I support does this all the time, although we tend to avoid doing it in our own code.
March 13, 2008 at 7:54 am
Yes... same problem as with Global Temp Tables... if same job runs more than once, BOOM on table creation or YECH on what happens to the data because more than 1 job is writing to/ reading from the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 7:58 am
Matt Miller (3/12/2008)
Jeff Moden (3/10/2008)
Giving away a fine "secret" here, but no one's ever heard of temporary stored procedures? Build it using dynamic SQL... execute it... it's all in the same scope that way. You can make temp tables with all sorts of variable column names that way... reporting on steriods... no RBAR... no global temp table or fixed table headaches.Does that work better than multiple Exec I was thinking of? Just curious. I can't say I've played with temp SP's...
They have the advantage of a "continuing scope" when compared to multiple EXEC's. You can do a lot of things to a temp table in a single proc. Not so true with multiple execs if the temp table was created in just one exec. Why create the temp table in a temporary proc? Variable columns for reporting purposes is one good use.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 8:06 am
Jeff Moden (3/13/2008)
They have the advantage of a "continuing scope" when compared to multiple EXEC's. You can do a lot of things to a temp table in a single proc. Not so true with multiple execs if the temp table was created in just one exec. Why create the temp table in a temporary proc? Variable columns for reporting purposes is one good use.
Hmm. All right, I can see that. Not sure I'm fully wrapping my head around the concept, but I'll have to throw that on the "I gotta play with that" pile, hopefully get to it at some point....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 7:59 pm
Matt Miller (3/13/2008)
Jeff Moden (3/13/2008)
They have the advantage of a "continuing scope" when compared to multiple EXEC's. You can do a lot of things to a temp table in a single proc. Not so true with multiple execs if the temp table was created in just one exec. Why create the temp table in a temporary proc? Variable columns for reporting purposes is one good use.Hmm. All right, I can see that. Not sure I'm fully wrapping my head around the concept, but I'll have to throw that on the "I gotta play with that" pile, hopefully get to it at some point....
Perhaps another strange use... have you ever had a proc over 4k that you really, really wanted to use sp_ExecSQL for? Enter the temporary stored procedure...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 8:51 pm
Yup - that's another good one. I'll have to experiment. It's not that I don't see how it could be used: it's more like I've somehow managed to keep my dynamic queries small enough not to have to use that.
After a few bad incidents of "hunt for the missing ' in a sea of dynamic SQL", I got somewhat more gunshy with LONG strands of dynamic. Of course - maybe I'm getting better, or the editor seems to highlight those better - I'm not having quite so many quote issues lately....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply