Share via

drop # table if exists statement does not dropping table the second time

J L 0 Reputation points
2023-04-07T15:03:15.3866667+00:00

Hi All, I have this in my store procedure.

DROP TABLE IF EXISTS #TMP_TABLE_01 SELECT GETDATE() [TODAY] INTO #TMP_TABLE_01 DROP TABLE IF EXISTS #TMP_TABLE_01

I am getting error message below. Msg 2714, Level 16, State 1, Line 9 There is already an object named '#TMP_TABLE_01' in the database. Completion time: 2023-04-07T10:57:59.7788259-04:00 Thank you in advance,

Azure SQL Database
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,061 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
SQL Server | Other

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2023-04-07T21:05:01.7266667+00:00

    I don't see the full code for your stored procedure, but I assume that you have SELECT INTO/CREATE TABLE for the same temp-table name in your procedure, this is not permitted.

    If you tell us more about what you want to achieve, we can help you with alternatives.

    0 comments
  2. Anonymous
    2023-04-10T02:11:38.5766667+00:00

    Hi @J L
    I ran the code shown above, but no error was reported. So maybe there are other factors in your stored procedure that cause the error. If you can show all the code, it will help solve the problem. Best regards, Percy Tang

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.