||SQL Server Tips by Gama and Naughter
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.
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.
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
EXEC @ret=spDivision1 5, 0
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
From now on let us test only the call that will cause the error:
DECLARE @ret int
EXEC @ret=spDivision2 5, 0
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:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter