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

 

RAISERROR
 

This statement will raise an error with a user defined error message. The error message is either created dynamically or stored in the system table sysmessages.

 

Example: same as before but raising an error message created dynamically.

 

CREATE PROCEDURE spDivision3 @num1 int, @num2 int

AS

--SP with error management code, an error is raised  + 1 row with 'Error'

IF @num2=0

      BEGIN

      SELECT 'Error'

      RAISERROR ('Error: Division by zero.', 16, 1)

      END

ELSE

      select @num1/@num2

 

RAISERROR does not change the return code, instead it will have to be changed with a RETURN statement.
 

If the error message is used in many SPs, to avoid inconsistencies due to changes in the message, the message can be stored in sysmessages. The system SP sp_addmessage will add the message and sp_dropmessage will drop it. User-defined error messages must have a msg_id greater or equal to 50001.

 

Example: same as before but raising an error message stored in sysmessages.
 

This is to store the new error message:

 

EXEC sp_addmessage 50001, 16, N'Error: Division by zero.'

 

CREATE PROCEDURE spDivision4 @num1 int, @num2 int

AS

--SP with error management code, an error is raised  + 1 row with 'Error'

IF @num2=0

      BEGIN

      SELECT 'Error'

      RAISERROR (50001, 16, 1)

      END

ELSE

      select @num1/@num2

 

RAISERROR will exit the current SP but it will still allow the execution of all the statements following it, within the same block. The next example shows RAISERROR and some statements that will execute after it.

 

CREATE PROCEDURE spDivision4a @num1 int, @num2 int

AS

--SP with error management code, an error is raised  + 1 row with 'Error'

IF @num2=0

      BEGIN

      SELECT 'Error'

      RAISERROR (50001, 16, 1)

      SELECT '1'

      RETURN -6

      SELECT '2'

      END

ELSE

      select @num1/@num2

      SELECT '3'

      RETURN 9

  

The SELECT '1' and RETURN 6 will execute because they are in the same block as the RAISERROR. The RETURN will cause an immediate exit from the SP.

 

Sometimes it is very hard to prevent the error from happening but it is still necessary to take some action.

 

@@ERROR
 

The  system function @@ERROR will return an error code if an error was encountered after the completion of the TSQL statement immediately preceding it, otherwise it will return 0, meaning success. The value of @@ERROR changes for each TSQL statement and the only way to keep track of errors is by using a temporary variable to store the error code. If there is no need to keep track of the error but simply act upon it, then the value of @@ERROR can be checked after the TSQL statement to be tested.

 

CREATE PROCEDURE spDivision5 @num1 int, @num2 int

AS

--SP with error management code, the error is detected, with @@Error, after it happens

DECLARE @errnum int

select @num1/@num2

SET @errnum=@@Error

IF @errnum<>0

      SELECT 'Error'

 

 The return code is changed automatically to store the latest @@Error value if no RETURN statement is present. Even if there are more statements after the error occurred, the error code is still preserved.
 


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