SQLTeam.com | Weblogs | Forums

DROP TABLE #tmptbl (Is this necessary?)


#1

Hello all sql expert,

Is that necessary to have drop table #tmptbl at

the of the query or stored proc if you already have

the if statement on the header to drop the #tmptbl?

For example:

IF OBJECT_ID ('tempdb.dbo.#tmptbl') IS NOT NULL DROP TABLE #tmptbl

SELECT A,B,C
INTO #tmptbl
FROM A_TABLE

DROP TABLE #tmptbl (Is this necessary?)

OR the above If statement already covers to clean up the #tmptbl?

Thanks guys


#2

If the temp table is created within the stored procedure it will get automatically dropped when the stored procedure completes. Having said that, the test for existence and the explicit drop at the end of procedures are still good ideas IMO. The test for existence is not a lot of overhead. The explicit drop adds nothing to the overhead since it is going to be done anyway.


#3

Thank you BustazKool.