How to break SQL script execution

I am working on sql script and I am having a requirement of stop continuing the script if some conditions are not satisfied.

When I Google it, I found the RaisError with 20 severity level will terminate it.
But for some reasons I cannot use that option.

Can please provide me what are the possible alternatives to stop SQL script execution.

From the RAISERROR documentation (emphasis mine):


Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.

It's highly likely the principal you're executing the script as does not meet these criteria.

There's nothing wrong with using RAISERROR; you're just using a severity level that's excessive. I use level 16 as a default for an error that is raised and the sequence will be terminated. If you want to be more accurate, you can follow the levels given by Microsoft itself:

Now, having said all that, depending on the context of the script, using RAISERROR may not be enough, as it doesn't "exit" the script by itself (using normal severity levels).

For example:

RAISERROR(N'Test', 16, 1);

SELECT 1;   /* Executed! */

This will both raise an error and return a result set.

To terminate the script immediately, I prefer to use RETURN (using GOTO-type constructs are generally discouraged in most programming circles where alternatives exist):

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */

Or handle the error using TRY/CATCH, which will cause execution to jump to the CATCH block if the severity is 11 or higher:

BEGIN TRY
    RAISERROR(N'Test', 16, 1);
    SELECT 1;   /* Not executed */
END TRY
BEGIN CATCH
    SELECT 2;   /* Executed */
END CATCH

BEGIN TRY
    RAISERROR(N'Test', 10, 1);
    SELECT 1;   /* Executed */
END TRY
BEGIN CATCH
    SELECT 2;   /* Not executed */
END CATCH

A separate problem is if the script spans multiple batches -- RETURN will only exit the batch:

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

SELECT 2;   /* Executed! */

To fix this, you can check @@ERROR at the start of every batch:

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

IF (@@ERROR != 0)
    RETURN;

SELECT 2;   /* Not executed */

Edit: As Martin Smith correctly points out in the comments, this only works for 2 batches. To extend to 3 or more batches, you can cascade raising errors like so (note: the GOTO method does not solve this problem as the target label must be defined within the batch):

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

IF (@@ERROR != 0)
BEGIN
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    RETURN;
END

SELECT 2;   /* Not executed */
GO

IF (@@ERROR != 0)
BEGIN
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    RETURN;
END

SELECT 3;   /* Not executed */

Or, as he also points out, you can use the SQLCMD method if that's appropriate for your environment.

StackExchange Database Administrators Q#48912, answer score: 10