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?

Guh? (padding to make my "Guh?" query at least 15 characters long) — Ian Boyd, Dec 07 '09 at 21:17
That this question even needs to be asked indicates that there's something slightly off about SQL, but then again, it's procedural methodology bolted on the side of a functional language, so what do you expect? — quillbreaker, Aug 17 '12 at 19:26

score 105 · Accepted Answer · edited Feb 07 '12 at 12:39

105

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.

edited Feb 07 '12 at 12:39

bluish

26,356
27
122
180

answered Dec 07 '09 at 21:13

AdaTheDev

142,592
28
206
200

1

Can you think of any reason why calling `return` would not unconditionally exit from a procedure? – Ian Boyd Dec 07 '09 at 21:18
8

Be careful return-ing out of a procedure if you've started a transaction or have an open cursor. – No Refunds No Returns Dec 07 '09 at 21:23
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 – AdaTheDev Dec 07 '09 at 21:25
2

Would this work in a TRY-CATCH? So if I call RETURN within the TRY, would it then execute the CATCH? – al_sweets Jan 08 '20 at 11:30

score 31 · Answer 2 · answered Dec 07 '09 at 21:10

31

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

answered Dec 07 '09 at 21:10

Andomar

232,371
49
380
404

1

"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 – Forgotten Semicolon Dec 07 '09 at 21:12
29

@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) – Andomar Dec 07 '09 at 21:14
1

+1. Not really an answer to this question, but "the answer is useful" – Ian Boyd Dec 07 '09 at 21:19

Forgotten Semicolon · Answer 3 · 2009-12-07T21:24:20.953

12

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.

edited Dec 07 '09 at 21:24

answered Dec 07 '09 at 21:10

Forgotten Semicolon

13,909
2
51
61

5

+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 – Ian Boyd Dec 07 '09 at 21:21
I really like this. but ... sigh ... something else new to learn. – No Refunds No Returns Dec 07 '09 at 21:22

Ian Boyd · Answer 4 · 2009-12-07T21:53:55.087

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.

of course you should never code an insert without specifying the columns. — HLGEM, Dec 08 '09 at 22:17
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. — Ian Boyd, Dec 09 '09 at 14:11

score 5 · Answer 5 · answered Dec 07 '09 at 21:27

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

this does seem a cleaner/faster way to type it out. – JDPeckham Sep 24 '12 at 20:00 — JDPeckham, Sep 24 '12 at 20:00

score 4 · Answer 6 · answered Sep 24 '12 at 19:58

This seems like a lot of code but the best way i've found to do it.

    ALTER PROCEDURE Procedure
    AS

    BEGIN TRY
        EXEC AnotherProcedure
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
        RETURN --this forces it out
    END CATCH

--Stuff here that you do not want to execute if the above failed.    

    END --end procedure

score 1 · Answer 7 · answered Dec 07 '09 at 22:00

1

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

answered Dec 07 '09 at 22:00

cjk

45,739
9
81
112

SQL Server 2000: How to exit a stored procedure?

7 Answers7