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?

Solution 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.

Solution 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

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Forgotten Semicolon]

"Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions." From MSDN

[Andomar]

@Forgotten Semicolon: Yeah, so in most cases, raiserror wins the prize for the function that functions in a way nobody expects (as well as the golden typo trophy)

[mistertodd]

Can you think of any reason why calling return would not unconditionally exit from a procedure?

[mistertodd]

+1. Not really an answer to this question, but "the answer is useful"

[mistertodd]

+1 SQL Server 2000, so doesn't help answer my question. But it can be a useful technique for someone trying to do exception handling

[No Refunds No Returns]

I really like this. but ... sigh ... something else new to learn.

[No Refunds No Returns]

Be careful return-ing out of a procedure if you've started a transaction or have an open cursor.

[AdaTheDev]

I can't - could you post a full sproc example that when you run it, does not exit immediately on the RETURN? Just so we have an exact example to review

[dburges]

of course you should never code an insert without specifying the columns.

[mistertodd]

Of course i wanted to code the insert without specifying columns. Now that i have to specify them, additional columns added to the source table will be lost.

[JDPeckham]

this does seem a cleaner/faster way to type it out.

[al_sweets]

Would this work in a TRY-CATCH? So if I call RETURN within the TRY, would it then execute the CATCH?