bitbucket-25253 (7/6/2012)

---

Emphasis (bolding) added by this poster

BrainDonor (7/6/2012)

---

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:

id i

NULL 2