Sql Server - stop or break execution of a SQL script

Simple and effective ways to halt execution

Want to yell "Cut!" on a SQL script? These practical tools and considerations will let you become the director of your script's fate.

The subtle art of saying "cut!" with "set noexec on/off"

Send subtler cues to your SQL script with set noexec on, like whispering to it to quit the act. When ready for an encore, set noexec off queues the next scene:

-- Opening scene
PRINT 'Romeo, where art thou?';

-- And...cut! 
SET NOEXEC ON;

-- Ghost scene
INSERT INTO LoveStory VALUES('Romeo', 'Juliet');

-- Back in action
SET NOEXEC OFF;


Over to 'RETURN' for a quiet exit

Direct your scripts more subtly using RETURN, making exits happen as smoothly as leaves falling:

IF @@ROWCOUNT = 0
BEGIN
    PRINT 'No rows to love, exiting...like the autumn leaf...';
    RETURN;
END


Local variables on the director's seat

IF clauses and local variables are your control panels for validation:

DECLARE @is_valid BIT = 1; -- Green light for the scene

-- Plot twist!
IF EXISTS(SELECT * FROM Drama WHERE Twist = 'Unworthy')
    SET @is_valid = 0;

-- Green light off, red light on
IF @is_valid = 0
    RETURN;


Special effects with "TRY...CATCH" blocks

Design dramatic twists paired with THROW, firing special effects on error:

BEGIN TRY
   -- Will he survive the fall?
   INSERT INTO Protagonists(ID, Name) VALUES(1, 'Hero');
END TRY
BEGIN CATCH
   -- Unfortunately, he forgot his parachute
   THROW;
END CATCH


"IF NOT EXISTS": The epic plot protector

Avoid narrative disasters with preventive checks before key scenes, ensuring your story remains consistent:

IF NOT EXISTS(SELECT * FROM Protagonists WHERE ID=1)
    INSERT INTO Protagonists(ID, Name) VALUES(1, 'Hero');
ELSE
    RAISERROR('Hero number 1 already exists, no clones allowed!', 16, 1);


Control flow and error handling: Mastering the art

Take your error handling and control flow to the next level with advanced techniques and tools.

Scripting perfection in "SQLCMD mode"

When sqlcmd.exe is your stage, :on error exit ensures perfect performances:

:on error exit
GO

SELECT * FROM BottomlessPit; -- A titanic error. No survivors. Script exits.


Kiddie pool to swimming in the deep - structured flags

For more complex plays, a structured flag variable simplifies the drama:

DECLARE @ControlFlags TABLE(FlagKey VARCHAR(100), FlagValue INT);
INSERT INTO @ControlFlags VALUES('IsValid', 1);

-- Plot deepens
IF EXISTS(...) UPDATE @ControlFlags SET FlagValue = 0 WHERE FlagKey = 'IsValid';

-- Check flags before climax
IF (SELECT FlagValue FROM @ControlFlags WHERE FlagKey = 'IsValid') = 0
    THROW 50001, 'Fallen hero, plot stopped.', 1;


New-age 'RAISERROR' for the non-admins

In scenarios where you're the director with no admin privileges:

BEGIN TRY
    -- Risking it all
    SELECT 1/0; -- Superstar can't do that
END TRY
BEGIN CATCH
    PRINT 'Star can't defy gravity, halting stunt.';
    SET NOEXEC ON;
END CATCH


Manage mini-plays within your script with 'GO'

Use GO to manage separate acts within the same script:

-- Act 1
SELECT 'In fair Verona, where we lay our scene';

-- Snafu, only stop subsequent acts
GO
RAISERROR('Alas! A plague on both your houses.', 16, 1);

GO
-- Act 2 doesn't stand a chance
SELECT 'All are punished';


Audience interaction with 'THROW'

THROW allows you to engage your audience with tailored error messages:

-- Audience check
IF NOT EXISTS(SELECT * FROM Audience WHERE Bored>'Extremely')
    THROW 50002, 'Wake up! Lost the crowd here.', 1;


Sharing the behind-the-scenes with 'PRINT'

Increase transparency by sprinkling PRINT statements, like explaining the scene to the audience:

-- Hush! The big reveal
PRINT 'Unmasking the phantom...';

-- And the crowd goes wild
PRINT 'The phantom was... the butler!';


Wisdom from the almighty MSDN

Microsoft Developer Network (MSDN) is like the Shakespeare of SQL. It tells you that THROW defaults to a user error with severity level 16, and many more secrets.