SQL Server 2000: How to exit a stored procedure?
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.
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
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.