EXCEPTION HANDLING

Error Handling

An exception means Runtime Error

In sql statement if any statement causes RunTime error then sql server return error msg and program execution is continued.

When any error occurs to display user friendly message and to execute error processing statement and the error must be handled.

To handle errors we need to provide a block called try-catch block

In sql server upto 2000 a system variable called @@error is used to get information about the raised error. But from sql server 2000 system variable called @@error is used to get the information about the raised error but from sql server 2005 onwards a set of functions are provided to get information about the raised error.

Syntax

Try

statement

end

catch

statements

end

example:

create procedure div(@x int, @y int)

as

begin

begin try

declare @z int

set @z=@x/@y

print @z

end try

begin catch

print 'divide by zero error'

end catch

end

Functions:

To get information about the raised error,

Sql server provides a set of functions, which are collectively called as error functions and they are as follows

Error_number()

This function is used to get the unique no of the error raised

Error_mesage()

Used to get the message related to the current user

Error_severity()

Returns the severity of the current error

Error_State()

Returns the state of the error

Error_procedure()

Returns the name of the stored sub program in which error occurs

Error_line()

Returns the line no in the stored sub program that causes error

create procedure div1(@x int, @y int)

as

begin

begin try

declare @z int

set @z=@x/@y

print 'ratio is ' +cast(@z as varchar)

end try

begin catch

print  error_number()

print error_message()

print error_severity()

print error_state()

print error_procedure()

print error_line()

end catch

end

exec div1 10,0

8134

Divide by zero error encountered.

16

1

div1

6

Every error has 4 properties to it, they are

 Msgid

Msg str

Severity

State

For example

Try the following statement

Print 100/0

This will display the following error message

Msg 8134,Level 16,state 1,

Divide by zero error encountered

In this the msgid

Of the message which is unique across server.

Message id less than 50000 is system messages

Getting list of all Error Messages

Select * from sys.messages

Adding custom message to built in error message list

Sp_addmessage

Sp_addmessage msgid,severity,msgtext

The following example adds an error message negative values are not allowed to the error message list of sql server

sp_addmessage 50001,9,'-ve values are not allowed'

Note:

Error no must be greater than 50000

The unique id of user defined errors must be greater than 50000 because error no up to 50000 is reserved for predefined error message of sql server.

sp_addmessage 50002,16,'cannot divide the no by one'

raiserror(50002,16,1)

Msg 50002, Level 16, State 1, Line 1

cannot divide the no by one

Deleting custom messages from Built in Error Messages List

You can delete user defined error messages added to predefined error messages of sql server and for this use the stored procedure

sp_dropmessage msgid

The following example deletes the error message with id 50001

sp_dropmessage 50001

sp_addmessage 50005,15,'employee already exists'

create procedure

insert_rec(@e int,@n varchar(20),@s smallmoney)

as

begin

begin try

if exists(select * from emp_temp whereeno=@e)

raiserror(50005,15,1)

if(@s<3000)

raiserror('sal>3000',15,1)

insert into emp_temp values(@e,@n,@s)

end try

begin catch

print error_message()

end catch

end

Raising Exception Manuals

There are some situations where system will not rise an exception for your requirement and you want to rise the exception Manual and for this use the function raiserror that has the following syntax

Raiserror(msgid/msgtxt,severity,state,[,arguments])

Range of severity can be from o to 25

0 to 18 system use

19 to 25 only dba  these are called fatal error, if this fatal error occurs client connection will be terminated.

19 to 25- administrator

State: the third argument state is used to identity exactly where the errors occurs in the program when the same error is raised multiple times with the same program

State values can be in the range of 0 to 255

Some error message of sql server contains format specifier like %d,%s

In, this case to specify what values to print in the place of those format specifier use arguments

sp_addmessage 50002,7,’division of %d with %d not possible

alter procedure div1(@x int, @y int)

as

begin

declare @r int

begin try

if(@x<0 or @y<0)

raiserror (50001,9,1)

else if @y=0

raiserror(50002,7,1,@x,@y)

else begin

set @r=@x/@y

print 'ratio is ' +cast(@r as varchar)

end

end try

begin catch

if ERROR_NUMBER()=50001

print '-ve values are not allowed'

else if ERROR_NUMBER()=50002

print error_message()

end catch

end

NOTE:

Now, if we want to customize the error message with formatting we can use the raiserror statement as following

Raiserror(‘cannot divide  %d with %d ‘,15,1,@x,@y)

In this case sustituing the values of variables @x at the first

We can also use the withlog option at the end of the string to write the error message into the sql server log file as following

Raiserror(‘cannot divide  %d with %d ,15,1,@x,@y) with log

After running the above procedure which will generate the error go and verify under the following location in object explorer of the management studio. i.e under the management node, sql server logs node,

Current node click on it, where we find the error message