Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
  SQL Server Tips by Gama and Naughter

 

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

AS

--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

AS

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

IF @num2=0

      BEGIN

      SELECT 'Error'

      RETURN -6

      END

ELSE

      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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

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