Tsql - How to Use Go Inside of a Begin .. End Block

TSQL - How to use GO inside of a BEGIN .. END block?

I had the same problem and finally managed to solve it using SET NOEXEC.

IF not whatever
BEGIN
    SET NOEXEC ON; 
END

ALTER TABLE dbo.EMPLOYEE ADD COLUMN EMP_IS_ADMIN BIT NOT NULL
GO
UPDATE dbo.EMPLOYEE SET EMP_IS_ADMIN = whatever

SET NOEXEC OFF; 

Use of Begin / End Blocks and the Go keyword in SQL Server?

GO is like the end of a script.

You could have multiple CREATE TABLE statements, separated by GO. It's a way of isolating one part of the script from another, but submitting it all in one block.

BEGIN and END are just like { and } in C/++/#, Java, etc.

They bound a logical block of code. I tend to use BEGIN and END at the start and end of a stored procedure, but it's not strictly necessary there. Where it IS necessary is for loops, and IF statements, etc, where you need more then one step...

IF EXISTS (SELECT * FROM my_table WHERE id = @id)
BEGIN
   INSERT INTO Log SELECT @id, 'deleted'
   DELETE my_table WHERE id = @id
END

Multiple statements inside one BEGIN ... END block

The column doesn't exist error is due to validation that occurs on existing objects. Since the table already exists, the parser / compiler will verify that the table contains all of the referenced columns.

In order to get around such timing issues with object verification, you can enclose the statement in an EXEC which will not be verified until run-time:

BEGIN
  ALTER TABLE [dbo].[UserProfiles]
    ADD [AllCheckboxesChecked] [bit]
    CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0
    NOT NULL;

  EXEC(N'UPDATE [dbo].[UserProfiles]
         SET [AllCheckboxesChecked]=1
         WHERE [CheckedBoxes] LIKE ''%#ALL#%''');
END;

How to ignore the GO statements in IF EXISTS block?

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.

TSQL: Executing a stored procedure inside a BEGIN...END block

From MSDN's EXECUTE Article:

You do not have to specify the EXECUTE keyword when executing stored
procedures if the statement is the first one in a batch.

Therefore, your second example throws the error because the stored procedure call is not the first statement in the batch.

Many inserts inside a begin-end block

To counter the huge else block, you could use any one the the following 3 strategies.

STRATEGY 1

Use Bulk insert from SQL Server. Just dump all your data into a csv file and use the following statement in your else block. Store the csv file on your computer and give it's UNC path after FROM in statement below. This way you will only have a single line in your else block.

BULK INSERT dbo.MyTable
FROM '\\share\somepath\myTableInsertData.csv'
WITH (FORMAT = 'CSV'); 

STRATEGY 2

Write a stored procedure that inserts only x rows at a time. You could then call this stored procedure in a While loop and your else block would be very small. You would call this stored procedure repeatedly in a while loop from your original SQL and then the else block would end being just a few lines of t-sql code.

Note that you can control how many rows are inserted by the stored procedure at a time by using an appropriate value for the variable @numberOfRowsAtaTime. I have used 1000 so in a single call of stored procedure 1000 rows get inserted.

Of course, based on your business rules you can script the stored procedure's insert statements. If you have a pattern in your INSERTS then you could script that pattern into the stored procedure logic below.

Stored Procedure

CREATE PROCEDURE dbo.insertXRows
    @startIndex INT,
    @numberOfRows INT 
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @counter INT;
    SET @counter = @startIndex;

    WHILE @counter < (@startIndex + @numberOfRows -1)
    BEGIN 
    -- Insert statements for rows goes here
    --INSERT FOR @counter -- @counter would be different for each iteration
      SET @counter =  @counter + 1;
    END
END
GO

Your SQL

IF EXISTS (SELECT * FROM [MyTable])
BEGIN
    PRINT 'No need to insert data'
    --Stop executing script
END
ELSE
BEGIN

    declare @insertRowCounter int;
    set @insertRowCounter = 1;
    declare @numberOfRowsAtaTime int;
    set @numberOfRowsAtaTime = 1000;
    WHILE @insertRowCounter <= 150000
      EXEC dbo.insertXRows @insertRowCounter, @numberOfRowsAtaTime -- insert 1000 rows at a time
      SET @insertRowCounter = @insertRowCounter + @numberOfRowsAtaTime; 
    END
END

STRATEGY 3

Come up with 10 stored procedures so that each stored procedure has 15000 INSERTS. Then simply call these 10 stored procedures from your else block.

10 stored procedures

CREATE PROCEDURE dbo.insertProc1
  AS
BEGIN

    SET NOCOUNT ON;

    --INSERT1
    --INSERT2

     --INSERT15000

    END
END
GO

CREATE PROCEDURE dbo.insertProc2
  AS
BEGIN

    SET NOCOUNT ON;

    --INSERT15001
    --INSERT15002

     --INSERT3000

    END
END
GO

Your SQL

IF EXISTS (SELECT * FROM [MyTable])
BEGIN
    PRINT 'No need to insert data'
    --Stop executing script
END
ELSE
BEGIN
   EXEC insertProc1
   EXEC insertProc2
   EXEC insertProc3
   EXEC insertProc4
   EXEC insertProc5
   EXEC insertProc6
   EXEC insertProc7
   EXEC insertProc8
   EXEC insertProc9
   EXEC insertProc10
END

BEGIN...END block in SQL Server

BEGIN / END delimit program statements.

Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed.

CASE blocks accept expressions.

Evaluates a list of conditions and returns one of multiple possible result expressions.

So you are trying to fit a square peg in a round hole.

Why can't I create a view inside of a BEGIN ... END block

It's because CREATE VIEW must be the first statement in a batch as described in this MSDN reference.

Instead, you could do:
e.g.

.....
    BEGIN 
        EXECUTE('CREATE VIEW [dbo].[dummy] AS SELECT 1 AS Dummy')
    END

can you have multiple GO's inside of an BEGIN TRY CATCH?

GO is a batch separator. The first GO inside a stored procedure ends the procedure's definition, IOW everything after the GO is not considered a part of the procedure. You cannot have a GO inside a procedure.

So, whatever you're trying to do, it's not the proper way.

Any harm in using BEGIN / END to organize SQL code?

You can do this. It's a good idea to structure code like that because it's integrated with the language and the IDE in a way that comments are not. In C languages I sometimes use {} blocks for that in case it is necessary to have larger methods.

I found the col/line style easier to read for me

If that's easier for you then it makes sense. But maybe it also makes sense to train your eyes to accept a style where many columns are in one line. This saves a tremendous amount of vertical space. More fits on the same screen and clarity increases.

---

Related Topics