February 2, 2002 at 3:18 am
Hi all,
In my project, I want to create #tables using dynamic sql.
When I try to do the same, it is not working. If you are using the same command for creating an ordinary table, it is working, Please help me.
execute sp_executesql N'create table #test(a int)' is not working and
execute sp_executesql N'create table test(a int)' is working perfectly.
thanking you
Kiran
February 2, 2002 at 7:35 am
The issue is the difference between local and global temporary tables. When we use a single #, the temporary table only stays around for the duration of your dynamic SQL execution. It's the same thing as:
CREATE PROC usp_CreateTable
AS
CREATE TABLE #MyTable (MyID int)
INSERT #MyTable VALUES (5)
GO
CREATE PROC usp_ReadTable
AS
SELECT * FROM #MyTable
GO
EXEC usp_CreateTable
EXEC usp_ReadTable
You'll get the Invalid object name '#MyTable' error. That's because #MyTable is automatically dropped when the first stored procedure goes out of scope. The same thing is happening to you executing the dynamic SQL query. The temporary table is getting dropped as that dynamic query goes out of scope.
The way around this is to use a global tempory table (two ## instead of one). For instance, this will work:
CREATE PROC usp_CreateTable
AS
CREATE TABLE ##MyTable (MyID int)
INSERT ##MyTable VALUES (5)
GO
CREATE PROC usp_ReadTable
AS
SELECT * FROM ##MyTable
DROP TABLE ##MyTable
GO
EXEC usp_CreateTable
EXEC usp_ReadTable
Hope this helps.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 2, 2002 at 4:28 pm
see
Access Temp Tables Across SPs
Which includes a bit on accessing temp tables created in d-sql.
Cursors never.
DTS - only when needed and never to control.
February 3, 2002 at 12:00 am
I too had the same problem. A #temp table is released once the batch in which it is created ends. EXEC hosts it's own batch and therefore a #temp table created within is released as soon as EXEC completes, leaving you with no table to work with. If you still want a local temp table and dont want to resort to a global temp table, i have found the following code to work fine:
SET @field = 'total'
SET @table = 'invoices'
SET @sql = 'SELECT MAX(' +@field+ ') FROM ' +@Table
CREATE TABLE #curTemp (maxamt INT)
INSERT INTO #curTemp
EXEC(@sql)
Using this method allows your SELECT to be dynamic but the temp table definition can be local to the rest of the procedure, and still not seen by other procedures. This works because #curTemp is created outside the EXEC statement. You are able to dynamically SELECT data into a #temp table without having to resort to a global (##temp table). This one had me going for a while some time back.
February 4, 2002 at 4:21 am
In a nutshell straight from SQL Books Online:
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. 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 which called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
February 4, 2002 at 7:53 am
Right, which means if he can wrap the dynamic SQL statement with a stored procedure which creates the temporary table there, he can go with #. However, if he can't, his only real option is ##.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 4, 2002 at 10:11 am
Or you can create a real table and use the SPID as a column. When you start the sproc, delete all rows with your @@SPID. Then insert your data, including your @@SPID as a value.
This is unique while you are connected. If your connections fails for some reason, the delete at the beginniing will clean up for you. This will prevent compilations of the sproc again as well as allow you to optimize and tune the table with placement and/or indexes.
Steve Jones
February 5, 2002 at 12:39 pm
My only concern with that previous by Steve or ## as by Brian is that only one user at a time can runn the procedure which is fine if that is the case. I would just keep this in mind as you can start stepping on yourself if you are not carefull.
February 5, 2002 at 1:22 pm
If you go with the permanent table and SPID approach, this isn't the case. SPID belongs to a connection. As a result, any given SPID is unique for a given instaneous period of time. The only thing, as Steve points out, to be concerned with is if for some reason a connection failed and left behind data based on the SPID in the table and another connection coming in and getting the same SPID. However, if there is cleanup DELETE before inserting into the table, this ceases to be an issue.
With ##tables, you are correct in that one only person would be able to use it as a time. Generally, I steer far clear of ##tables (in fact, I try to steer clear of #table when I can) but if he needs the table to persist and he can't create it in a wrapper stored procedure outside of the dynamic SQL statement, the # option certainly isn't going to work unless he stacks statements.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply