Call now: (800) 766-1884  


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA

 SQL Server Scripts
 Scripts Menu




  SQL Server Tips by Gama and Naughter


SP error management

Stored procedures react to errors based on the error’s severity. If the severity is too low, the error is a low level warning and the error code must be stored immediately or it will be reset by other TSQL commands. If the severity is too high, the stored procedure will generate an error and terminate its execution. The error will also terminate any objects that called the crashed stored procedure.


Return code

System stored procedures will return an error code if an error was encountered during its processing, otherwise they will return 0, meaning success. This is a standard and it should be applied to production code because many applications expect the return code to store an error code.


Example: One SP with no error management and the return codes from it, with and without an error.

CREATE PROCEDURE spDivision1 @num1 int, @num2 int


--SP with no error management code.

select @num1/@num2


Declaring one variable to store the returned value and calling the SP with valid input first and next with input that will result in a division by zero:


DECLARE @ret int

EXEC @ret=spDivision1 5, 2

PRINT @ret

EXEC @ret=spDivision1 5, 0

PRINT @ret


The return value indicates an error and so no rows are returned. If there were output parameters, their value would be NULL. In some scenarios, it would be preferable to return a row with an error. The RETURN statement will also cause an immediate and unconditional exit from the SP. It is common to use RETURN as a way of ending the execution flow; it is particularly useful in nested statements, avoiding a GOTO EndSPlbl: or similar.


Example: One SP with error management; the input is validated and if the divisor is zero then one row will return with the value “Error” and the return code will be changed to –6 (division by zero).


CREATE PROCEDURE spDivision2 @num1 int, @num2 int


--SP with error management code, the error code is returned + 1 row with 'Error'

IF @num2=0


      SELECT 'Error'

      RETURN -6



      select @num1/@num2


From now on let us test only the call that will cause the error:


DECLARE @ret int

EXEC @ret=spDivision2 5, 0

PRINT @ret


Setting no error flags and showing no error messages might be useful when trying to handle errors quietly. Certain severity levels will close the connection; others allow the errors to be stored in the database log. Each application has its own requirements about error management. Certain errors are insignificant and end up ignored; others will create either a warning or an error message. It depends on how serious the error is, considered by the system or the developers. Errors considered serious by the system are usually not easy to control with TSQL but such errors are a response to a situation where the server, database, object or connection was either unresponsive or its reliability became compromised.

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter


Burleson Consulting Remote DB Administration







Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.

Burleson Consulting
SQL Server database support


Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter