SQL Server - stop or break execution of a SQL scri

Question

Is there a way to immediately stop execution of a SQL script in SQL server, like a "break" or "exit" command?

I have a script that does some validation and lookups before it starts doing inserts, and I want it to stop if any of the validations or lookups fail.

Answer 1

None of these works with 'GO' statements. In this code, regardless of whether the severity is 10 or 11, you get the final PRINT statement.

Test Script:

-- =================================
PRINT 'Start Test 1 - RAISERROR'

IF 1 = 1 BEGIN
    RAISERROR('Error 1, level 11', 11, 1)
    RETURN
END

IF 1 = 1 BEGIN
    RAISERROR('Error 2, level 11', 11, 1)
    RETURN
END
GO

PRINT 'Test 1 - After GO'
GO

-- =================================
PRINT 'Start Test 2 - Try/Catch'

BEGIN TRY
    SELECT (1 / 0) AS CauseError
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage
    RAISERROR('Error in TRY, level 11', 11, 1)
    RETURN
END CATCH
GO

PRINT 'Test 2 - After GO'
GO

Results:

Start Test 1 - RAISERROR
Msg 50000, Level 11, State 1, Line 5
Error 1, level 11
Test 1 - After GO
Start Test 2 - Try/Catch
 CauseError
-----------

ErrorMessage
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Divide by zero error encountered.

Msg 50000, Level 11, State 1, Line 10
Error in TRY, level 11
Test 2 - After GO

The only way to make this work is to write the script without GO statements. Sometimes that's easy. Sometimes it's quite difficult. (Use something like IF @error <> 0 BEGIN ....)

Answer 2

If you are simply executing a script in Management Studio, and want to stop execution or rollback transaction (if used) on first error, then the best way I reckon is to use try catch block (SQL 2005 onward). This works well in Management studio if you are executing a script file. Stored proc can always use this as well.

Answer 3

You can alter the flow of execution using GOTO statements:

IF @ValidationResult = 0
BEGIN
    PRINT 'Validation fault.'
    GOTO EndScript
END

/* our code */

EndScript:

Answer 4

Thx for the answer!

raiserror() works fine but you shouldn't forget the return statement otherwise the script continues without error! (hense the raiserror isn't a "throwerror" ;-)) and of course doing a rollback if necessary!

raiserror() is nice to tell the person who executes the script that something went wrong.

Answer 5

I would not use RAISERROR- SQL has IF statements that can be used for this purpose. Do your validation and lookups and set local variables, then use the value of the variables in IF statements to make the inserts conditional.

You wouldn't need to check a variable result of every validation test. You could usually do this with only one flag variable to confirm all conditions passed:

declare @valid bit

set @valid = 1

if -- Condition(s)
begin
  print 'Condition(s) failed.'
  set @valid = 0
end

-- Additional validation with similar structure

-- Final check that validation passed
if @valid = 1
begin
  print 'Validation succeeded.'

  -- Do work
end

Even if your validation is more complex, you should only need a few flag variables to include in your final check(s).

Answer 6

This was my solution:

...

BEGIN
    raiserror('Invalid database', 15, 10)
    rollback transaction
    return
END