SQL Server 2000: How to exit a stored procedure?

Question

How can I exit the center of a stored procedure?

I have a stored procedure where I want to help out earlier (when trying to debug). I tried calling RETURN and RAISERROR , and sp continues to work:

 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 that it works because I again encounter an error. I do not see any of my prints . If I comment on the main part 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 will not 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 can I get rid of the stored procedure in SQL Server?

Answer 1

You can use RETURN to immediately stop the execution of a stored procedure. Quote taken from Books Online :

Exits unconditionally from a request or procedure. RETURN is immediate and complete and can be used anywhere to exit a procedure, batch or. Statements that follow RETURN are not followed.

From paranoia, I tried an example, and it prints the SEALs and immediately stops execution.

Answer 2

If you do not specify a severity of 20 or higher, raiserror will not stop execution. See the MSDN documentation.

The usual workaround is to include return after each raiserror :

 if @whoops = 1 begin raiserror('Whoops!', 18, 1) return -1 end 

Answer 3

Put it in TRY/CATCH .

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

Link: MSDN .

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

Answer 4

I understood why RETURN not unconditionally returning from a stored procedure. The error I see is that the stored procedure was compiled - not when it was executed.

Consider an imaginary stored procedure:

 CREATE PROCEDURE dbo.foo AS INSERT INTO ExistingTable EXECUTE LinkedServer.Database.dbo.SomeProcedure 

Despite the fact that this stord procedure contains an error (perhaps this is due to the fact that the objects have the number of differnet columns, the table may have a timestamp column, maybe the stored procedure does not exist), you can still save it. You can save it because you are referencing a linked server.

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

My error does not occur on line 114, it is on line 114. SQL Server cannot compile the stored procedure, so it does not work.

And this is why RETURN not coming back, since it has not yet started .

Answer 5

It works 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 6

This seems like a lot of code, but the best way I've found for this.

  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 

Answer 7

Because you do not have BEGIN and END statements. You should not see fingerprints or errors executing this statement, only Statement Completed (or something like that).