Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2006-05-23 : 22:35:09
|
| hi, is there anyway for me to check whether the global temporary table (e.g. ##temptbl) is exist. i want to do so because sometimes my stored procedure failed before it completes and the global temp table will not be dropped. So, the next time when i try to execute the stored procedure, sql server complains that ##temptbl is already exist. So, i need a way to check if this table exist and drop it before i run the stored procedure.thanks for any reply.aex |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-23 : 22:46:22
|
You should consider changing your procedure to not use a global temp table. It is a very bad practice, and a security hole.This will drop it for you.if object_id('tempdb..##temptbl') is not null drop table ##temptblCODO ERGO SUM |
 |
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2006-05-23 : 23:03:24
|
Thanks Michael for the great reply. Thanks also to let me know the drawback of using global temp table. Anyway, i hv no much choice for that because i need a list of value (which i store in global temp table) to be used by several stored procedures. If i create a local temp table, that table is only available to the particular stored procedure only.  aex |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-23 : 23:12:09
|
quote:
Originally posted by aex
Thanks Michael for the great reply. Thanks also to let me know the drawback of using global temp table. Anyway, i hv no much choice for that because i need a list of value (which i store in global temp table) to be used by several stored procedures. If i create a local temp table, that table is only available to the particular stored procedure only. aex
If you create a local temp table in a stored procedure, it is available to any store procedure that it calls.CODO ERGO SUM |
|
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2006-05-23 : 23:24:11
|
Thanks again Michael. I really do not aware that the local temp table is available to the other store procedures that is called. Thanks for the advice.  aex |
|
|
|
|
|
|
How to check whether temp table exist?