February 22, 2006 at 2:22 am
hi,
im having problems in creating a temp table using sp_executesql as i need to pass the table name in the query using a variable.
my current code is:
DECLARE @sql as nchar(4000)
SET @sql= 'CREATE TABLE #tmpm'+ @Table_Name1 + ' (column_names type)'
EXEC sp_executesql @sql
but in this code it is not creating the table
am i doing something wrong.
please help me....
viral
February 22, 2006 at 6:21 am
It is a scope thing...if you create the temp table like this, it will be visible only within that scope of execution, example:
SET NOCOUNT ON
DECLARE @sql as nvarchar(4000), @table_name1 nvarchar(100)
set @table_name1 = 'test'
SET @sql= 'CREATE TABLE #tmpm'+ @Table_Name1 + ' (col1 int); insert into #tmpm'+ @Table_Name1 +' values (1); select * from #tmpm'+ @Table_Name1 +''
EXEC sp_executesql @sql
select * from #tmpmtest
col1
-----------
1
Server: Msg 208, Level 16, State 1, Line 6
Invalid object name '#tmpmtest'.
So, in the first step, it shows the data because the temp table is visible in that scope...outside of that execution it is not visible and thus it fails.
Hth
February 26, 2006 at 9:34 pm
thanx a lot.
ya this has solved my problem.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply