CODEWITHSUNDEEP
sql-servert-sql
Nate Pet
Nate Pet

Reputation: 46222

Invalid object name #temp

I am getting the following message

     Invalid object name #alert

on alternate run of a stored procedure that has the following code:

    IF OBJECT_ID('tempdb..#alert') IS NOT NULL
      DROP TABLE #alert
    ELSE
     create table #alert(order varchar(50))

    BULK INSERT #alert
    FROM 'C:\OrderImport\NewOne.txt'
    WITH 
    (
       FIELDTERMINATOR ='\t',
       ROWTERMINATOR ='\n',
       FIRSTROW = 2
     )

Upvotes: 2

Views: 5657

Answers (2)

M.Ali
M.Ali

Reputation: 69494

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

 create table #alert(order varchar(50))      

BULK INSERT #alert
FROM 'C:\OrderImport\NewOne.txt'
WITH 
(
   FIELDTERMINATOR ='\t',
   ROWTERMINATOR ='\n',
   FIRSTROW = 2
 )

Just remove the ELSE and it will check for Existance of the table , If it does exist it will drop it and then Create a new one, you dont need and ELSE statement here . Because If it doesnt Exist only then the contorl will jump to ELSE block, but if it does exist the Control will get in IF Block Drops the table and skips ELSE Block , since in IF .. ELSE Block only one block of code will execute. 

IF OBJECT_ID('tempdb..#alert') IS NOT NULL  --<-- if not null      
 BEGIN
    DROP TABLE #alert                       --<-- Drops the table 
 END
ELSE
  CREATE TABLE                             --<-- This statement Never gets executed 
                                             -- since the control fell in the 1st block
                                            -- it never goes into ELSE Block

If you do want to use IF.. ELSE Blocks you can do something like this ...

IF OBJECT_ID('tempdb..#alert') IS NOT NULL        
 BEGIN
    DROP TABLE #alert
    create table #alert(order varchar(50))
 END
ELSE
  BEGIN
    create table #alert(order varchar(50))
  END

Upvotes: 2

rory.ap
rory.ap

Reputation: 35260

Your logic is causing #alert to be dropped every other time you run the SP. The first time you run it, it enters the ELSE block and creates the #alert table and therefore the rest of the procedure succeeds. The next time it runs, it enters the IF block and drops the table that was created previously, and therefore the BULK INSERT #alert fails.

Upvotes: 1

Related Questions