Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Why is raising a error unacceptable? Also is this script a stored procedure? –  Namphibian Aug 29 '13 at 6:01
    
I did not clearly understand your fist question. For second question; no this is not a SP –  New Developer Aug 29 '13 at 6:06
    
What is the script? Does it comprise multiple batches? Have you seen the answers here? –  Martin Smith Aug 29 '13 at 9:29

2 Answers 2

You can utilize the GOTO statement to skip around wherever you want. In other words, you run into an error or some other condition, and you can have a label at the bottom of the script (i.e. TheEndOfTheScript:) and just issue a goto TheEndOfTheScript; statement.

Here is a quick sample:

print 'here is the first statement...';

print 'here is the second statement...';

-- substitute whatever conditional flow determining factor
-- you'd like here. I have chosen a dummy statement that will
-- always return true
--
if (1 = 1)
    goto TheEndOfTheScript;

print 'here is the third statement...';

print 'here is the fourth statement...';


TheEndOfTheScript:
print 'here is the end of the script...';

The output of this execution will be the following:

here is the first statement...
here is the second statement...
here is the end of the script...

As you can see, the GOTO has skipped printing the third and fourth statements and jumped right to the label (TheEndOfTheScript).

share|improve this answer

From the 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:

enter image description here

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.

share|improve this answer
    
That last suggestion doesn't work. See pastebin. I like the sqlcmd method here –  Martin Smith Aug 29 '13 at 14:15
1  
@MartinSmith: Fixed. Thanks. –  Jon Seigel Aug 29 '13 at 16:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.