CODEWITHSUNDEEP
sqltemp-tables
rock
rock

Reputation: 605

Error Using Temporary Tables With Same Names SQL Server

Here is my scenario. (Following is my stored proc taking @date as an input parameter)

DECLARE @date DATE

If object_id('tempdb..#TempList') is not null drop table #TempList
go

Create table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1), 
FOOTAGE DECIMAL(12,4))

If @date IS NULL

Insert into #TempList
Select mill_no, null, bl_status,footage from fn_A(0,0)

Select * from #TempList


If object_id('tempdb..#TempList') is not null drop table #TempList
go

Create Table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1),
FOOTAGE DECIMAL(12,4))

If @date IS NOT NULL

Insert into #TempList
Select * from fn_B(0,'06/06/2006')

Select * from #TempList

I figured out from one of the posts that I cannot use temporary tables with same names unless I inclide a GO. However, including GO is not taking the parameters I try to pass.

Is there an alternate approach to eliminate this error?

Upvotes: 1

Views: 2234

Answers (3)

rock
rock

Reputation: 605

I struggled all these days to insert values into same temporary table when a given condition is met.

Given that I am on a project migrating FoxPro(which has cursors declared all over) to SQL Server this small logic needed to be implemented in multiple stored procs.

Finally, what I learned out of this is - Think Straight Before Trying Anything Different (suggestion to my co-beginners in DB migration).

DECLARE @date DATE

SET @date = '06/06/2006'    --@date = null

If object_id('tempdb..#TempList') is not null drop table #TempList

Create table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1), 
FOOTAGE DECIMAL(12,4))

If @date = null

-- Here I am inserting null in place of Ship_Date because fn_A returns only 3 columns in my case
Insert into #TempList
Select mill_no, null, bl_status,footage from fn_A(0,0)

--Select * from #TempList

else

Insert into #TempList
Select * from fn_B(0,@date)

Select * from #TempList

Thanks everyone for giving your inputs. Hope this helps somebody.

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

if object_id('tempdb..#TempList') is allways NULL because #TempList is not the name that results created on tempdb's sysobjects table when you do create table #TempList

EDIT
what about this: 

CREATE PROC PIRULO(@date as DATE) as

Create table #TempList (MILL_NO VARCHAR(7), SHIP_DATE DATE, BL_STATUS NCHAR(1), FOOTAGE DECIMAL(12,4))
IF @date IS NULL
    Insert into #TempList
    Select mill_no, null, bl_status,footage from fn_A(0,0)
ELSE
    Insert into #TempList
    Select * from fn_B(0,'2006/06/06')    -- also I changed date order.

Select * from #TempList

Upvotes: 0

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

Based on the procedure supplied, you could use TRUNCATE TABLE instead.

I don't see a point dropping and recreating a table if all you want to do is quickly remove the records.

EDIT

You don't drop and recreate your table with the same name; instead of this code:

If object_id('tempdb..#TempList') is not null drop table #TempList go  
Create Table #TempList (
    MILL_NO VARCHAR(7), 
    SHIP_DATE DATE, 
    BL_STATUS NCHAR(1), 
    FOOTAGE DECIMAL(12,4)
)

Just do this:

TRUNCATE TABLE #TempList

Upvotes: 2

Related Questions