Hi,
I need the use Create table #table command in the Exec command
Example
declare @.chr_CreateSql nVarchar(4000)
,@.err Varchar(255)
select @.chr_CreateSql = ' Create table #test (ss varchar(255))'
Exec (@.chr_CreateSql)
select * from #test
When I run this I get the following error
But when I change #test to ##test
It works fine.
The reason I need the create table in exec command the number of columns in
temp table is dynamic. Please help me to resolve this issue
Thanks
Senthil> select @.chr_CreateSql = ' Create table #test (ss varchar(255))'
> Exec (@.chr_CreateSql)
> select * from #test
Every day, it seems.
EXEC() has its own scope. When EXEC() has finished, your #temp table no
longer exists.
You can fool it by doing this all in one EXEC() call:
EXEC('CREATE TABLE #test(ss VARCHAR(255)); SELECT * FROM #test')
Or, figure out a way to drop the temp table, or the dynamic SQL, or both.
Neither are considered "best practices"... dynamic SQL is treated quite well
here: http://www.sommarskog.se/dynamic_sql.html
> But when I change #test to ##test
This is a global temp table. I recommend you do not use this syntax unless
you know the pros and cons.
> The reason I need the create table in exec command the number of columns
> in
> temp table is dynamic.
Why not use all the columns and then ignore the ones you don't need.
A
No comments:
Post a Comment