Emphasis (bolding) added by this poster
That explanation requires expanding slightly, because there is more than one type of temporary table.
Local temporary tables (prefixes with '#') go out of scope when the exec command completes. However, use '##' to create a Global temporary table and the table can be accessed outside of the exec statement that created it.
At the completion of the command given in the QOD
exec ('create table #qotd2 (id int))'
follows the above since the exec command completes, that is #qotd2 has gone out of scope.
To illustrate, the code below has been modified so that the entire sequence of commands is contained within the scope of the exec command.
exec ('create table #qotd2 (id int)
alter table #qotd2 add i int
insert into #qotd2 (i) values (2)
select * from #qotd2
drop table #qotd2')
The above then will return for the SELECT statement the values:
If everything seems to be going well, you have obviously overlooked something.
Please help us, help you -before posting a question please read Before posting a performance problem please read