If statement - TSQL-CodePudding

What I'm trying to achieve is to check if a table exists:

if it exists, just truncate it
if it does not exist, create the table

Below is my code but I get an error.

Code:

--Check if table exists
IF  EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[Com_SQL_Server_Agent_Monitor]') 
              AND type in (N'U'))
    TRUNCATE TABLE [dbo].[Com_SQL_Server_Agent_Monitor]
ELSE
    --Create table if not exist
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO
 
    CREATE TABLE [dbo].[Com_SQL_Server_Agent_Monitor]
    (
        [job_id] [uniqueidentifier] NULL,
        [originating_server] [nvarchar](30) NULL,
        [name] [nvarchar](128) NULL,
        [enabled] [tinyint] NULL,
        [description] [nvarchar](512) NULL,
        [start_step_id] [int] NULL,
        [category] [nvarchar](128) NULL,
        [owner] [nvarchar](128) NULL,
        [notify_level_eventlog] [int] NULL,
        [notify_level_email] [int] NULL,
        [notify_level_netsend] [int] NULL,
        [notify_level_page] [int] NULL,
        [notify_email_operator] [nvarchar](128) NULL,
        [notify_netsend_operator] [nvarchar](128) NULL,
        [notify_page_operator] [nvarchar](128) NULL,
        [delete_level] [int] NULL,
        [date_created] [datetime] NULL,
        [date_modified] [datetime] NULL,
        [version_number] [int] NULL,
        [last_run_date] [int] NULL,
        [last_run_time] [int] NULL,
        [last_run_outcome] [int] NULL,
        [next_run_date] [int] NULL,
        [next_run_time] [int] NULL,
        [next_run_schedule_id] [int] NULL,
        [current_execution_status] [int] NULL,
        [current_execution_step] [nvarchar](128) NULL,
        [current_retry_attempt] [int] NULL,
        [has_step] [int] NULL,
        [has_schedule] [int] NULL,
        [has_target] [int] NULL,
        [type] [int] NULL
    ) ON [PRIMARY]
GO

This is the error I get:

There is already an object named 'Com_SQL_Server_Agent_Monitor' in the database

Any ideas what I'm missing?

CodePudding user response：

The biggest problem in your code is the fact that you have multiple SQL statements in your ELSE block - but they're not framed by a BEGIN ... END.

So really what you have right now is:

IF  EXISTS (....)
    TRUNCATE TABLE [dbo].[Com_SQL_Server_Agent_Monitor]
ELSE
    SET ANSI_NULLS ON

-- these statements will be executed ALWAYS - no matter what the
-- IF EXISTS() check returns!    
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Com_SQL_Server_Agent_Monitor]
......

So even if the table exists, and gets truncated - the SET QUOTED_IDENTIFIER ON and the CREATE TABLE statement will still be executed! That's why you're getting an error "table already exists".

What you need to do is:

IF EXISTS (....)
    TRUNCATE TABLE [dbo].[Com_SQL_Server_Agent_Monitor]
ELSE
BEGIN
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    CREATE TABLE [dbo].[Com_SQL_Server_Agent_Monitor]
    ......
END