is it necessary to drop temp table in stored proc
in [Programming SQL]
|
Prev: Que Assignments
Next: year week table help
From: rodchar on 16 Apr 2010 09:50 Hi All, IF object_id('tempdb..#TEMP_CORE_DATA') IS NOT NULL BEGIN DROP TABLE #TEMP_CORE_DATA END Is the preceding sql necessary when the temp table is put in a stored procedure? thanks, rodchar
From: Tom Moreau on 16 Apr 2010 09:58 That depends. If the table was created within the proc, the proc will automatically be dropped when the proc terminates. However, if the temp table already existed prior to the proc call, and you wanted to drop the table before proceeding with a section of code within the called proc, then it would be necessary. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message news:92DC01B2-674F-479A-BF26-C6BCDED4735E(a)microsoft.com... Hi All, IF object_id('tempdb..#TEMP_CORE_DATA') IS NOT NULL BEGIN DROP TABLE #TEMP_CORE_DATA END Is the preceding sql necessary when the temp table is put in a stored procedure? thanks, rodchar
From: Gary Fletcher on 16 Apr 2010 10:30 If the stored procedure created the local temp table it disappears when that procedure exits. "rodchar" wrote: > Hi All, > > IF object_id('tempdb..#TEMP_CORE_DATA') IS NOT NULL > BEGIN > DROP TABLE #TEMP_CORE_DATA > END > > Is the preceding sql necessary when the temp table is put in a stored > procedure? > > thanks, > rodchar
From: rodchar on 16 Apr 2010 10:58 thanks all for the help, rod. "rodchar" wrote: > Hi All, > > IF object_id('tempdb..#TEMP_CORE_DATA') IS NOT NULL > BEGIN > DROP TABLE #TEMP_CORE_DATA > END > > Is the preceding sql necessary when the temp table is put in a stored > procedure? > > thanks, > rodchar
From: Gert-Jan Strik on 17 Apr 2010 16:51
My personal opinion is, that if you create a temp table (unconditionaly) at the beginning of a stored procedure, then you should drop it at the end (also unconditionaly). Maybe nothing will go wrong if you don't drop it, but IMO not cleaning up your own code is lazy and unprofessional. You might have guessed by now that I don't like to rely on garbage collection. -- Gert-Jan rodchar wrote: > > Hi All, > > IF object_id('tempdb..#TEMP_CORE_DATA') IS NOT NULL > BEGIN > DROP TABLE #TEMP_CORE_DATA > END > > Is the preceding sql necessary when the temp table is put in a stored > procedure? > > thanks, > rodchar |