Error Handling

          The T-SQL command RAISERROR allows us as developers to have the ability to produce our own SQL Server error messages when running queries or stored procedures. We are not tied to just using error messages that come with SQL Server; we can set up our own messages and our own level of severity for those messages. It is also possible to determine whether the message is recorded in the Windows error log or not.

          Severity levels range from 1 at the innocuous end to 25 at the fatal end. Severity levels of 2 to 14 are generally informational. Severity level 15 is for warnings and levels 16 or higher represent errors. Severity levels from 20 to 25 are considered fatal, and require the WITH LOG option, which will mean that the error is logged in the Windows error log and the connection terminated.

There are three options that could be placed at the end of the RAISERROR message. These are the WITH options:

• LOG will place the error message within the Windows error log.

• NOWAIT sends the error directly to the client.

• SETERROR will reset the error number to 50000 within the message string only.

There is a system stored procedure, sp_addmessage, that can create a new global error message that can be used by RAISERROR by defining the @msgnum.

The parameters into this system stored procedure are as follows:

@msgtext: to add message to the error

@msgnum: The number of the message, typically will be greater than 50000.

@severity: Same as the preceding, in a range of 1 to 25.

@lang: Use this if you need to define the language of the error message. Normally will be   left empty.

@with_log: Set to 'TRUE' if you wish to write a message to the Windows error log.

@replace: Set to 'replace' if you are replacing an existing message and updating any of the preceding values with new settings.

select 1/0

if @@error=8134

raiserror ('zero error',20,1) with log

If is_srvrolemember ('serveradmin') = 0 

 begin 

  raiserror (15247,-1,-1) 

  return

 end