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.

David Harder (7/6/2012)

---

This question made up for yesterday's beating. Glad we ending the week with this one.

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.

exec ('create table #qotd2 (id int))'

exec ('create table #qotd2 (id int)
     alter table #qotd2 add i int
     insert into #qotd2 (i) values (2)
     select * from #qotd2 
     drop table #qotd2')

id         i
NULL       2