Wednesday, October 7, 2009

Error Handling in SQL

How will you handle Errors in Sql Stored Procedure?

SQL Provides a system variable named as @@ERROR, If any error occured in query execution, then error code will be set to @@ERROR, if there is no error, by default it holds the 0 value. so based on this value we can handle the error in SQL

INSERT dbo.emp VALUES (@Column2)

IF @@ERROR <>0
PRINT 'Error Occured'

The given code will print the following error

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@Column2".

For further info about this variable, have a look at the following link

How will you raise an error in sql?
RAISERROR - Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.


No comments:

Post a Comment