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

sql

dynamic-sql

try-catch

sql-execution

byAlex Kataev·Sep 30, 2024

-- Use GO to batch SQL commands, not within BEGIN...END which encapsulates a single batch.
-- Instead, end one batch with GO and start another. For instance:

-- Batch 1:
BEGIN
    PRINT 'First batch, ready to roll!'; -- Your code here
END
GO

-- Batch 2:
BEGIN
    PRINT 'Second batch, here we go again!'; -- Your code here
END

Essential: A game of GO and BEGIN...END; GO terminates a batch of play, BEGIN...END keeps it running smoothly within the same game. For multiple games, have multiple GOs.

Dynamic SQL: Mighty Knight to the Rescue

When the traditional GO falls short, a new champion emerges: dynamic SQL. This mightier knight allows us to separate SQL commands as strings and execute them using sp_executesql.

IF (some_condition)
BEGIN
    -- Here's unleashing the mighty Knight in shining armor!
    EXEC sp_executesql N'ALTER TABLE MyTable ADD NewColumn INT;'; -- Who's the boss now
    EXEC sp_executesql N'UPDATE MyTable SET NewColumn = 0;'; -- Updating like a boss! 
END

Dynamic SQL: Breaking barriers of the traditional GO, ensuring you've still got it going.

The Alternative Saga: SET NOEXEC

When traditional methods prove inadequate, we turn to the mystical power of SET NOEXEC. The ON/OFF switch, enabling or disabling the execution of T-SQL operations, making conditional control a breeze.

IF (some_condition)
BEGIN
    -- Flip the switch to the dark side, watch SQL statements skip past like a midnight dream.
    SET NOEXEC ON;
END
-- SQL Statements here will suffer a sad skip if the condition is true.

SET NOEXEC OFF; -- And, we're back to the bright side. Execute away!

Brace for Errors with TRY and CATCH

Real scripts own up to the risks and gracefully handle errors. When it gets risky, we use TRY and CATCH blocks, especially with dynamic SQL.

BEGIN TRY
    EXEC sp_executesql N'Some complex T-SQL code'; -- Pouring some complexity into SQL's life.
    -- A few more daring dynamic jumps.
END TRY
BEGIN CATCH
    PRINT 'An error occurred: ' + ERROR_MESSAGE(); -- Well, SQL has feelings too!
    -- The guarding strategy for when SQL trips.
END CATCH

Beware of Knowledge Nuggets: In this game, ERROR_MESSAGE() and ERROR_LINE() are our trusted allies. Use them, master them!

Play it Safe, Check for Column Existence

Ensure your scripts' safety. Assuming the existence of certain database objects, like columns, is risky. Play safe with the COL_LENGTH function. Avoid those awkward errors when updating invisible columns!

IF COL_LENGTH('TableName', 'ColumnName') IS NOT NULL
BEGIN
    UPDATE TableName
    SET ColumnName = 'NewValue' -- Peace of mind while updating? Priceless!
END

Safety first: Ensuring your script's resilience by preventing runtime errors goes a long way!

Classy Code with Organized T-SQL Statements

A well-organized script is like a pleasure drive. Group related operations logically within BEGIN...END blocks for the smoothest ride.

BEGIN
    -- A lovely group of related operations, all in one cozy block.
END
-- Possible return of the 'GO' command.
BEGIN
    -- Yet another cozy group of related operations.
END

Always be a step ahead with the flow of your script for ease of reading and technical accuracy.

Tips for Tactful Scripting

Catch errors with grace. Rollback transactions upon errors within dynamic SQL to maintain data integrity.
Pinpoint the perfect commit points for a happy data state.
Test well for the versions you're targeting. Ensure compatibility for peaceful coding.
Dive deep into official documentation long after the sun has set. Keep up with evolving features.