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

Put it in a TRY/CATCH.

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block

Reference: MSDN.

EDIT: This works for MSSQL 2005+, but I see that you now have clarified that you are working on MSSQL 2000. I'll leave this here for reference.

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

Answer 3

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 4

This works over here.

ALTER PROCEDURE dbo.Archive_Session
    @SessionGUID int
AS 
    BEGIN
     SET NOCOUNT ON
        PRINT 'before raiserror'
        RAISERROR('this is a raised error', 18, 1)
        IF @@Error != 0 
            RETURN
        PRINT 'before return'
        RETURN -1
        PRINT 'after return'
    END
go

EXECUTE dbo.Archive_Session @SessionGUID = 1

Returns

before raiserror
Msg 50000, Level 18, State 1, Procedure Archive_Session, Line 7
this is a raised error

Answer 5

i figured out why RETURN is not unconditionally returning from the stored procedure. The error i'm seeing is while the stored procedure is being compiled - not when it's being executed.

Consider an imaginary stored procedure:

CREATE PROCEDURE dbo.foo AS

INSERT INTO ExistingTable
EXECUTE LinkedServer.Database.dbo.SomeProcedure

Even though this stord proedure contains an error (maybe it's because the objects have a differnet number of columns, maybe there is a timestamp column in the table, maybe the stored procedure doesn't exist), you can still save it. You can save it because you're referencing a linked server.

But when you actually execute the stored procedure, SQL Server then compiles it, and generates a query plan.

My error is not happening on line 114, it is on line 114. SQL Server cannot compile the stored procedure, that's why it's failing.

And that's why RETURN does not return, because it hasn't even started yet.

Answer 6

Its because you have no BEGIN and END statements. You shouldn't be seeing the prints, or errors running this statement, only Statement Completed (or something like that).