CODEWITHSUNDEEP
sql-servert-sql
Michel Ayres
Michel Ayres

Reputation: 5985

GO commands in the middle of an IF statement

I was looking on SO about create tables only if they do not exist on the current DataBase (to be able to create it in different databases that MAY or MAY NOT have them already) and found those two helpful topics

So I made this query

IF (NOT EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_NAME = 'EMAILCONTAS'))
BEGIN
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EMAILCONTAS](
    [NRSEQEMAILCONTAS] [numeric](8, 0) NOT NULL,
    [CDEMAILCONTAS] [varchar](40) NULL,
    [MSGEMAILCONTAS] [varchar](4000) NOT NULL,
    [CCOEMAIL] [varchar](100) NULL,
    [NRSEQOPERADORA] [numeric](8, 0) NOT NULL,
 CONSTRAINT [PK_EMAILCONTAS] PRIMARY KEY CLUSTERED 
(
    [NRSEQEMAILCONTAS] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[EMAILCONTAS]  WITH CHECK ADD FOREIGN KEY([NRSEQOPERADORA])
REFERENCES [dbo].[OPERADORA] ([NRSEQOPERADORA])
GO

ALTER TABLE [dbo].[EMAILCONTAS]  WITH CHECK ADD  CONSTRAINT [FK_EMAILCONTAS_OPERADORA] FOREIGN KEY([NRSEQOPERADORA])
REFERENCES [dbo].[OPERADORA] ([NRSEQOPERADORA])
GO

ALTER TABLE [dbo].[EMAILCONTAS] CHECK CONSTRAINT [FK_EMAILCONTAS_OPERADORA]
GO
END

But when I execute it, I got this, in the error list.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ON'.

But it creates my table anyway (I put a "Select * from PERSON;" after the above code, to check if the error may block the next script to compile or not. And the error blocked it. Showing this error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Select'.

There is a way to avoid it?). And when I execute this query and the table already exist I got the follow errors.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ON'.
Msg 2714, Level 16, State 6, Line 2
There is already an object named 'EMAILCONTAS' in the database.
Msg 2714, Level 16, State 5, Line 2
There is already an object named 'FK_EMAILCONTAS_OPERADORA' in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

How could I accomplish this without getting those errors? Is there a way that I can create multiple code like this without problems? What am I doing wrong?

Upvotes: 0

Views: 811

Answers (2)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

GO is similar to the end of a script; you can remove the GOs from inside your BEGIN-END statement.

If you absolutely need to have GO in the code, you can break up your query as:

IF (criteria)
BEGIN
    ...
END

GO

IF (newcriteria)
BEGIN
    ...
END

Upvotes: 2

EthanSpitz
EthanSpitz

Reputation: 171

Where I work, all scripts must have that check. I have all the settings before the check like so in this example:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimTime]') AND type in (N'U'))
    BEGIN
        Create Table [dbo].[DimTime]
        (
            TimeID int PRIMARY KEY IDENTITY(1,1),
            Month int,
            Year int,
            Day int,
        )
    END

Upvotes: 1

Related Questions