SQL Server 2000: How to exit a stored procedure?

Question

How can I exit in the middle of a stored procedure?

I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on running:

CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS      print 'before raiserror'     raiserror('this is a raised error', 18, 1)     print 'before return'     return -1     print 'after return'  [snip] 

I know it keeps running because I encounter an error further down. I don't see any of my prints. If I comment out the bulk of the stored procedure:

CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS      print 'before raiserror'     raiserror('this is a raised error', 18, 1)     print 'before return'     return -1     print 'after return'     /*      [snip]    */ 

Then I don't get my error, and I see the results:

before raiserror Server: Msg 50000, Level 18, State 1, Procedure Archive_Session, Line 5 this is a raised error before return 

So the question is: how do I bail out of a stored procedure in SQL Server?

Answer 1

You can use RETURN to stop execution of a stored procedure immediately. Quote taken from Books Online:

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

Out of paranoia, I tried yor example and it does output the PRINTs and does stop execution immediately.

Answer 2

Unless you specify a severity of 20 or higher, raiserror will not stop execution. See the MSDN documentation.

The normal workaround is to include a return after every raiserror:

if @whoops = 1     begin     raiserror('Whoops!', 18, 1)     return -1     end