If i create a ##table using my stored procedure, I can do the select * from ##table


but when i created the #table using the stored procedure and tried to do the select * from #table


I am getting the error message invalid object #table


that means it is going out of scope.


But if i create the ##table, will that table going to remain in the database even until i drop the ##table or it gets dropped when i close the database connection.


Thank you very much.


-Raj

There are two types of temporary tables: 


Local temporary tables 


The names of these tables begin with one number sign (#). These tables are visible only to the connection that created them.


Global temporary tables 


The names of these tables begin with two number signs (##). These tables are visible to all connections. If the tables are not dropped explicitly before the connection that created them disconnects, they are dropped as soon as all other tasks stop referencing them. No new tasks can reference a global temporary table after the connection that created it disconnects. The association between a task and a table is always dropped when the current statement completes executing; therefore, global temporary tables are usually dropped soon after the connection that created them disconnects.

Darrell Norton, MVP
Darrell Norton's Blog


Please mark this post as answered if it helped you!