SQL Server Temp Table Already Exists? [duplicate]

Question

So this is weird, I am declaring a temp table in this bit of SQL, but I am only declaring it once based upon the if else logic. I am deleting the temp table before running the below query, and still get the same behavior.

However, SQL Server is complaing with There is already an object named '#ManifestTrackingBranches' in the database. , when I try to run the query with my ReportType set to 2.

Am I missing something here?

T-SQL

declare @ReportType int 
declare @CustomerNumber int 
declare @StartDate datetime
declare @EndDate datetime 

set @ReportType = 2
set @CustomerNumber = 81 
set @StartDate = '2014-04-27'
set @EndDate = '2014-05-04'

if @CustomerNumber = 81
begin
    if @ReportType = 1 -- roll up by location
    begin 
        select  InboundData.Tracking,
                InboundData.NegotiatedRate 
        into    #ManifestTrackingBranches
        from    InboundData
        where   Injected >= @StartDate and Injected <= @EndDate

        -- Match tracking numbers against Ebill Data 
        select      #ManifestTrackingBranches.Tracking,
                    SUM(isnull(cast(#ManifestTrackingBranches.NegotiatedRate as decimal(18,2)),0)) as ManifestAmount
        from        EBillData 
        group by    #ManifestTrackingBranches.Branch
    end

    else if  @ReportType = 2 -- Line Item Reports
    begin 
        select  InboundData.Tracking,
                InboundData.NegotiatedRate 
        into    #ManifestTrackingBranches
        from    InboundData
        where   Injected >= @StartDate and Injected <= @EndDate

        -- Match tracking numbers against Ebill Data 
        select      #ManifestTrackingBranches.Tracking,
                    SUM(isnull(cast(#ManifestTrackingBranches.NegotiatedRate as decimal(18,2)),0)) as ManifestAmount
        from        EBillData 
    end
end

The error happens on the second if where ReportType is set to 2, and I attempt to select into the same temp table.

Answer 1

Add this line of code before any declaration of variables.

IF OBJECT_ID('tempdb..#ManifestTrackingBranches') IS NOT NULL 
DROP TABLE #ManifestTrackingBranches
GO

Will only have affect if this statement is in a separate batch, using GO key word. Good enough when you are actually writing your procedure and testing the code by executing it again n again.

Inside your procedure you cannot add the key word GO and neither there is a need for dropping a table when this procedure is called from an application. every call to this procedure will have its own connection and will create a temp table limited to that connection's scope.

Edit

Since SQL Server 2016 and later versions you can also use the following syntax:

DROP TABLE IF EXISTS #ManifestTrackingBranches
GO

As the syntax suggests, it will drop the table if exists else do nothing but the command will not error out and code execution will move to the next line. A much cleaner and simpler approach.

The DROP IF EXISTS also works with other SQL Server objects too, such as View, Indexes, triggers and function etc.

Answer 2

SQL will retain temp tables per connection, unless you drop them. Therefore, it is aways a good idea to drop your temp table once you are done using it.

Add a DROP TABLE statements

declare @ReportType int 
declare @CustomerNumber int 
declare @StartDate datetime
declare @EndDate datetime 

set @ReportType = 2
set @CustomerNumber = 81 
set @StartDate = '2014-04-27'
set @EndDate = '2014-05-04'

if @CustomerNumber = 81
begin
  if @ReportType = 1 -- roll up by location
  begin 
    select  InboundData.Tracking,
            InboundData.NegotiatedRate 
    into    #ManifestTrackingBranches
    from    InboundData
    where   Injected >= @StartDate and Injected <= @EndDate

    -- Match tracking numbers against Ebill Data 
    select      #ManifestTrackingBranches.Tracking,
                SUM(isnull(cast(#ManifestTrackingBranches.NegotiatedRate as decimal(18,2)),0)) as ManifestAmount
    from        EBillData 
    group by    #ManifestTrackingBranches.Branch;

    --clean up after yourself
    drop table #ManifestTrackingBranches
  end

  else if  @ReportType = 2 -- Line Item Reports
  begin 
    select  InboundData.Tracking,
            InboundData.NegotiatedRate 
    into    #ManifestTrackingBranches
    from    InboundData
    where   Injected >= @StartDate and Injected <= @EndDate

    -- Match tracking numbers against Ebill Data 
    select      #ManifestTrackingBranches.Tracking,
                SUM(isnull(cast(#ManifestTrackingBranches.NegotiatedRate as decimal(18,2)),0)) as ManifestAmount
    from        EBillData 

    --clean up after yourself
    drop table #ManifestTrackingBranches
  end
end