whatwhatwhat whatwhatwhat - 4 months ago 18
SQL Question

#temp table not being dropped

I have this code that should drop a temp table if it exists, but I'm still getting an error:

Cannot drop the table '#jobsconsumed', because it does not exist or you do not have permission.
Could someone help me out? My I.T. admin doesn't think it's a permissions issue.

IF (SELECT object_id('TempDB.#jobsconsumed')) IS NULL
BEGIN
DROP Table #jobsconsumed
END

Answer
IF (SELECT object_id('TempDB.#jobsconsumed')) IS NULL
    BEGIN
    DROP Table #jobsconsumed
END 

The above code will enter Begin clause , only when TempTable is present..

To check and drop TempTable,correct way is below

IF object_id('Tempdb..#test') is Not null  
 is same as
  IF object_id('Tempdb.dbo.#test') is Not null  
Drop Table #test

There is no need of Begin And END clause in this case,since IF Will execute immediate statement when true

some tests on schema aspects of TEMP tables..

  use tempdb; 

 create schema hr

 create table hr.#t( c int) --this will work
 create table #t( c int) --this will fail

 create table #t1 --no schema ,so it will create a temp table in DBO Schema by default.

 --To drop the table
 drop table #t --this will drop across all schemas