CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Liam neesan
Liam neesan

Reputation: 2551

There is already an object named '#TempTableName' in the database?

I have a stored Procedure, I am moving final records to my temp table in IF ELSE statement

Example

IF(Condition)
BEGIN

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

     SELECT *
     INTO #Records
     FROM TableName

     SELECT * FROM #Records
END
ELSE
    IF OBJECT_ID('tempdb..#Records') IS NOT NULL  DROP TABLE #Records

     SELECT *
     INTO #Records
     FROM TableName

     SELECT * FROM #Records
END

My StoredProcedure is exactly the same as the above format. It throws the error There is already an object named '#Records' in the database.

Upvotes: 0

Views: 1971

Answers (3)

Ketan Kotak
Ketan Kotak

Reputation: 957

because while compiling SP by SQL object already found in IF condition and same time you are creating same table name in else. so it won't work.

I generally use following way

SELECT top 0 *
         INTO #Records

    IF(Condition)
    BEGIN

    INSERT INTO #Records
         SELECT *
         FROM TableName

         SELECT * FROM #Records
    END
    ELSE
        INSERT INTO #Records
         SELECT *
         FROM TableName

         SELECT * FROM #Records
    END

DROP TABLE #Records

You just execute DROP TABLE #Records first and then try to run this SP

Upvotes: 0

KDM
KDM

Reputation: 144

Try this will help you

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

SELECT top 1 * INTO #Records FROM TableName where 1=0;

IF(1=1)
  BEGIN

   INSERT INTO #Records
   select * FROM TableName;

   SELECT * FROM #Records;
 END
ELSE
 BEGIN

   INSERT INTO #Records
   select * FROM TableName;

   SELECT * FROM #Records;
 END

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

Place your if condition out of the if block....

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


 IF(Condition)
BEGIN
     SELECT *
     INTO #Records
     FROM TableName
     SELECT * FROM #Records
END
ELSE
     SELECT *
     INTO #Records
     FROM TableName
     SELECT * FROM #Records
END

OR

Just drop this temp table first in your db once then create your stored procedure

  drop table #records

Execute this then create your stored procedure.

Upvotes: 1

Related Questions