When you try to create temporary tables with dynamic SQL you will run into a scoping problem.
for example:
DECLARE @SQL nvarchar(4000)
SELECT @SQL = 'CREATE TABLE #Temp (col1 int)'
EXEC (@SQL)
SELECT * FROM #Temp
This will cause an error:
Msg 208, Level 16, State 0, Line 4
Invalid object name '#Temp'.
The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed.
One solution I have found for this problem is creating the table in the "parent" scope and then just using dynamic sql to modify the table. For this to work a table is created with a minimum set of colums. And then we use the ALTER TABLE statement with dynamic SQL. The Child session has access to the objects created in the parent session so the table can be modified with dynamic sql:
DECLARE @SQL NVARCHAR(4000)
CREATE TABLE #Temp ( id int null)
SELECT @SQL = 'ALTER #Temp ADD Col1 int null'
EXEC (@SQL)
SELECT * FROM #Temp
DROP TABLE #Temp
8 comments:
Thanks, this was driving me crazy.
Buddy Stein
Cheltenham High '69
Hi, thanks for this post, its exactly what I'm looking for. Unfortunately I can't get it to work.
Not Sure What I'm doing wrong but this did not work for me. The #temp referred to in my dynamic SQL doesn't appear to be the same #temp created in the calling code.
To make things interesting, I pre-fill #temp to demonstrate the issue.
#######
start
#######
Declare @dynamicSQLString varchar(4000), @QueryString varchar(1500)
drop table #temp --so I can keep re-running this.
select Top(1) * into #temp
From Transactions
Set @QueryString = ' [Customer ID] = 2 '
Set @dynamicSQLString = 'SELECT row_number() over (ORDER BY SettlementDate ASC) as idx, * into #temp
FROM Transactions Where ' + @QueryString + ' select * from #temp'
Exec(@dynamicSQLString)
select * from #temp
#######
end
#######
Notice there are two selects, first in the dynamic code, then in the outer code.
The Dynamic code's Select returns 11k records, the second select returns 1 record.
Any ideas? Thanks.
jfish222 -
Your problem is not with the dynamic SQL but with the insert syntax you are using.
If you are using insert into without specifying column names SQL server will create a new table.
In order to do what you want (adjusted to some tables I have in one of my DBs):
declare @dynamicSQLString varchar(4000), @QueryString varchar(1500)
DROP table #temp --so I can keep re-running this.
CREATE Table #temp (uid int null)
INSERT INTO #temp
(uid)
select Top(1) uid
From [Transaction]
Set @QueryString = ' [CustomerID] = 1 '
Set @dynamicSQLString = '
ALTER TABLE #temp ADD Col1 int null'
Exec(@dynamicSQLString)
Set @dynamicSQLString = '
INSERT INTO #temp
(uid, Col1)
SELECT row_number() over (ORDER BY uid ASC) as idx, uid
FROM [Transaction] Where ' + @QueryString + ' select * from #temp'
Exec(@dynamicSQLString)
select * from #temp
Worked like a charm! Thank you for your help. I'm in the process of teaching myself SQL, and there are definitely nuances that I've yet to master.
Again thank you.
Another option is to scope your temp table globally. See example below.
DECLARE @TableName varchar(256)
SET @TableName = '##TEST'
exec ('
CREATE TABLE ' + @TableName + '
(
[Id] [int],
[AnotherId] [int],
[SomeField] [varchar](256)
)')
exec ('SELECT * FROM ' + @TableName)
exec ('DROP TABLE ' + @TableName)
Hi Sagi,
I 'm facing the following issue.
Invalid object name '#ins'.
I don't get the above exception when running from the query explorer window. But i get this exception when executing from .NET Code. The following is my situation where i use dynamic sql:
SELECT * INTO #ins FROM INSERTED
SELECT * INTO #del FROM DELETED
SELECT @field = 0, @maxfield = MAX(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2, @bit - 1)
SELECT @CHAR = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(), @CHAR, 1) & @bit > 0 OR @Type in ('I', 'D')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') = @field
--Columns not to be audited
IF @fieldname NOT IN ('TransactionType', 'UserID', 'CreatedDate', 'LastModifiedBy', 'LastModifiedOn')
BEGIN
DECLARE @auditBody XML
DECLARE @ParmDefinition nvarchar(500)
--SELECT @sql = 'INSERT INTO ' + @AuditTableName + ' (ProgramCode, AttributeChanged, ChangedFrom, ChangedTo, ChangedDate, ChangedBy)'
SELECT @sql = 'SELECT @auditBodyOUTPUT = (SELECT COALESCE(i.ProgramCode, d.ProgramCode) AS ProgramCode '
SELECT @sql = @sql + ',''' + @fieldname + ''' AS AttributeChanged '
SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),d.' + @fieldname + ') AS ChangedFrom '
SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),i.' + @fieldname + ') AS ChangedTo '
SELECT @sql = @sql + ',''' + @UpdateDate + ''' AS ChangedDate '
SELECT @sql = @sql + ', COALESCE(i.LastModifiedBy, d.LastModifiedBy) AS ChangedBy'
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + ' on i.ProgramCode = d.ProgramCode'
SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' IS NOT NULL)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' IS NULL)'
SELECT @sql = @sql + ' FOR XML AUTO, ELEMENTS)'
SET @ParmDefinition = N'@auditBodyOUTPUT xml output'
PRINT @SQL
INSERT INTO BROKER_TEST VALUES (@auditBody, 'Before EXEC Trigger')
EXEC sp_executesql @sql, @ParmDefinition, @auditBodyOUTPUT = @auditBody OUTPUT
INSERT INTO BROKER_TEST VALUES (@auditBody, 'Trigger')
PRINT CAST(@auditBody as NVARCHAR(MAX))
EXEC BROKER_SendProgramAuditData @auditBody
END
END
END
DROP TABLE #ins
DROP TABLE #del
Can you guide me here.
Thanks and Regards,
Hemant.
A couple of questions:
1.
SELECT * INTO #ins FROM INSERTED
SELECT * INTO #del FROM DELETED
This follows an update I am guessing - is the update running in the same execuution batch?
2.
Are you sending all your SQL code as one batch or are you sending it over several times over the same connection? If that is the case - the requests might come on a different connection.
Please let me know
If the latter is the case
If you use Global Temporary tables you can access them in different scopes, even though you created them inside the scope of the dynamic sql. This is possible because sql will only drop the table after the scope that created it, and all the others that reference it are destroyed
For instance:
declare @sqlvar as varchar(max)
set @sqlvar = '
create table ##TempTable (name varchar(max))
insert into ##TempTable (name) select ''teste'''
exec (@sqlvar)
select * from ##TempTable
drop table ##TempTable
Post a Comment