Oleg Netchaev (12/9/2010)
... In other words, if create temp table part were moved out then the temp table would be visible to the exec batch and after, i.e.
if object_id('tempdb.dbo.#tmp') is not null drop table #tmp;create table #tmp (id int);
declare @sql varchar(2000);
set @sql = 'insert into #tmp(id) values (1);';
exec (@sql);
select * from #tmp;
results in
id-----------
1
Oleg
Another way to get it to work is to put it all on the inside of the dynamic SQL like this:
Declare @strSql varchar(2000);
Set @strSql = '';
Set @strSql = @strSql + 'Create table #tmp (id int);';
Set @strSql = @strSql + 'Insert into #tmp(id) values (1);';
Set @strSql = @strSql + 'Select * From #tmp;';
Exec (@strSql);