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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] SQL Server 2000: How to exit a stored procedure?

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to sql-server-2000

Examples related to control-flow