How to ignore the GO statements in IF EXISTS block?

Question

I am trying to execute a script based on IF NOT EXISTS condition. But, the block within BEGIN and END gets executed even if IF NOT EXISTS returns 1. I cannot change statements within BEGIN and END blocks. How to handle this situation?

IF NOT EXISTS(SELECT 1 FROM [dbo].[UPGRADEHISTORY] WHERE SCRIPTNAME='001-MarkSubmitted.sql' AND RELEASENUMBER= '1')
BEGIN
    IF NOT EXISTS(SELECT 1 FROM [dbo].[Action] WHERE Name='mark As Submitted')
        BEGIN
        SET IDENTITY_INSERT [dbo].[Action] ON 
        INSERT INTO [dbo].[Action](Id,Name,CreatedBy,CreatedOn) VALUES (6,'mark As Submitted',1,getdate())
        SET IDENTITY_INSERT [dbo].[Action] OFF
        END
    GO
    INSERT INTO [dbo].[StatusActionMapping](ArtifactType,StatusId,ActionId,RoleId) VALUES ('Report',11,6,1)
    GO

    INSERT INTO [dbo].[UpgradeHistory] ([ReleaseNumber],[ScriptNumber],[ScriptName],[ExecutionDate]) VALUES (1, (SELECT FORMAT(MAX(SCRIPTNUMBER) + 1, 'd3') FROM UpgradeHistory WHERE ReleaseNumber= 1),'001-MarkSubmitted.sql',GETDATE());
END
GO

Does this answer your question? [TSQL - How to use GO inside of a BEGIN .. END block?](https://stackoverflow.com/questions/6376866/tsql-how-to-use-go-inside-of-a-begin-end-block) — Amira Bedhiafi, Feb 03 '21 at 12:02
`GO` isn't a T-SQL operator, it's something interpreted by your IDE/CLI as a batch separator. If you don't want to separate the statement into separate batches (clearly here you don't) don't put the `GO`s in there. The solution is simple, fix your code by removing them as what you have is invalid syntax if you were to separate them into true separate batches (i.e. separate files). — Thom A, Feb 03 '21 at 12:05
I cannot the remove the GO statements within BEGIN and END blocks. The issue is the statements get executed even if NOT EXISTS is 1 — user3441903, Feb 03 '21 at 12:09
*"I cannot the remove the GO statements within BEGIN and END blocks."* Why not? Why would you want to separate a batch in the middle of a `BEGIN...END`? — Thom A, Feb 03 '21 at 12:10

Thom A · Accepted Answer · 2021-02-03T12:35:07.580

As I mention in the comment, GO is not a Transact-SQL operator, it's interpreted by your IDE/CLI as a batch separator.SQL Server Utilities Statements - GO:

SQL Server provides commands that are not Transact-SQL statements, but are recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code Editor. These commands can be used to facilitate the readability and execution of batches and scripts.

GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

The answer is simply, remove the GOs. There is no need for them here; it's because they are there that you are getting the error because separating the statements into different batches makes no sense here. What you have would be equivalent to having the following 3 "files" and trying to run them independently:

File 1:

IF NOT EXISTS(SELECT 1 FROM [dbo].[UPGRADEHISTORY] WHERE SCRIPTNAME='001-MarkSubmitted.sql' AND RELEASENUMBER= '1')
BEGIN
    IF NOT EXISTS(SELECT 1 FROM [dbo].[Action] WHERE Name='mark As Submitted')
        BEGIN
        SET IDENTITY_INSERT [dbo].[Action] ON 
        INSERT INTO [dbo].[Action](Id,Name,CreatedBy,CreatedOn) VALUES (6,'mark As Submitted',1,getdate())
        SET IDENTITY_INSERT [dbo].[Action] OFF
        END

Would error due to a BEGIN with out END.

File 2:

INSERT INTO [dbo].[StatusActionMapping](ArtifactType,StatusId,ActionId,RoleId) VALUES ('Report',11,6,1)

This would run fine.

File 3:

INSERT INTO [dbo].[UpgradeHistory] ([ReleaseNumber],[ScriptNumber],[ScriptName],[ExecutionDate]) VALUES (1, (SELECT FORMAT(MAX(SCRIPTNUMBER) + 1, 'd3') FROM UpgradeHistory WHERE ReleaseNumber= 1),'001-MarkSubmitted.sql',GETDATE());
END

Would error due to an END without a BEGIN.

There's nothing in your query that will causing a parsing error like you're adding a new column to an existing table and reference it later in the batch, so there's no need to separate the batches. Just remove the GOs in the middle of your BEGIN...ENDs and this works as you require:

IF NOT EXISTS (SELECT 1
               FROM [dbo].[UPGRADEHISTORY]
               WHERE SCRIPTNAME = '001-MarkSubmitted.sql'
                 AND RELEASENUMBER = '1')
BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM [dbo].[Action]
                   WHERE Name = 'mark As Submitted')
    BEGIN
        SET IDENTITY_INSERT [dbo].[Action] ON;
        INSERT INTO [dbo].[Action] (Id, Name, CreatedBy, CreatedOn)
        VALUES (6, 'mark As Submitted', 1, GETDATE());
        SET IDENTITY_INSERT [dbo].[Action] OFF;
    END;
    INSERT INTO [dbo].[StatusActionMapping] (ArtifactType, StatusId, ActionId, RoleId)
    VALUES ('Report', 11, 6, 1);

    INSERT INTO [dbo].[UpgradeHistory] ([ReleaseNumber], [ScriptNumber], [ScriptName], [ExecutionDate])
    VALUES (1, (SELECT FORMAT(MAX(SCRIPTNUMBER) + 1, 'd3') --This is a REALLY bad idea. Use an IDENTITY or SEQUENCE
                FROM UpgradeHistory
                WHERE ReleaseNumber = 1), '001-MarkSubmitted.sql', GETDATE());
END;
GO

Also note my point on your final INSERT. FORMAT(MAX(SCRIPTNUMBER) + 1, 'd3') is going to end up with race conditions. Use an IDENTITY or SEQUENCE.

`WITH (UPDLOCK, HOLDLOCK)` on the first select would sort out race condition. But agreed, the identity insert is a bad idea. — Charlieface, Feb 03 '21 at 13:07

How to ignore the GO statements in IF EXISTS block?

1 Answers1

File 1:

File 2:

File 3: