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.