How to do Error Handling in SQL Server?
How to Raise Error Manually in SQL Server?
How to ADD USER Defined Error Messages in sys.messages catalog view?
How to do Error Handling in SQL Server?
When you have a batch of SQL statements and in that batch of SQL Statements if any SQL statement causes error then it throws the error message but it never terminates the execution of program it continues the execution of SQL statements which comes after that SQL Statement which raised error.
Let’s understand the above problem with an example
I am creating a store procedure which divides two numbers and print the result. As you can see from the below query, I am passing two number in the procedure. I am dividing them and storing the result in variable named as @RESULT. Initially I am assigning 0 values for the @RESULT variable and later updating the variable value with the result.
CREATE DATABASE TEACHMESQLSERVER
USE TEACHMESQLSERVER
GO
CREATE PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECOND INT)
AS
BEGIN
DECLARE @RESULT INT
SET @RESULT=0
SET @RESULT=@FIRST/@SECOND
PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)
END
Let’s execute the above procedure
EXEC DIVISION_BY_ZERO 20,10
As you can see from the result below; when we divide 20 by 10 we get 2 and our procedure prints the result as below.
Now let’s try to divide 20 by 0; its universal truth that we can’t divide any number by 0 and we will get error.
EXEC DIVISION_BY_ZERO 20,0
Here you go; we got below error message after executing the above query.
Error message clearly says that divided by zero error encountered. If you have noticed after the error message that Print Statement executed and it printed THE OUTPUT IS: 0. this is wrong right?
It should not have printed this.
To overcome this problem SQL server provides Error handling, when we do error handling the SQL statement which cause the error will be terminated and the execution of stored procedure or batch will also be stopped.
In T-SQL programming error handling is done by using TRY and CATCH construct. Errors in T-SQL code can be processed using a TRY-CATCH construct. A TRY -CATCH construct consists of two parts TRY block and CATCH block. TRY Block contains the SQL statement which may cause error, when an error situation is detected in TYRY block, control is passed to a CATCH block where it can be processed or handled. After the CATCH block handles the exception (Error), control is then transferred to the first T-SQL statement that follows the END CATCH statement. A TRY block always starts with the BEGIN TRY statement and ends with END TRY statement. A CATCH block always starts with a BEGIN CATCH statement and ends with END CATCH statement. A CATCH block must follow a TRY block. In T-SQL each TRY block is associated with only one CATCH block.
Let’s do error handling in above procedure.
ALTER PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECCOND INT)
AS
BEGIN
DECLARE @RESULT INT
SET @RESULT=0
BEGIN TRY
SET @RESULT=@FIRST/@SECCOND
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)
END
As you can see from the above code, we have kept SET @RESULT=@FIRST/@SECOND in TRY Block. It is because this line of SQL statement is creating dividing by zero error. We have CATCH block next to this TRY block. CATCH block is handling the error raised by its TRY block.
Let’s try to divide 20 by 0.
GO
EXEC DIVISION_BY_ZERO 20,0
As you can from the above output, this time we didn't get any error message but we get a message saying Divide by Zero encountered and it also terminates the execution and didn't print THE OUTPUT IS:0 like last time.
How to Raise Error Manually in SQL Server?
We can also raise errors manually at some required situations. It is used to return messages back to application using the same format as a system error or warning message is generated. For raising an error manually we use RAISERROR statement. RAISERROR can return either a user defined error message that has been created using the SP_ADDMESSAGE system stored procedure or a message string specified in the RAISERRORstatement.
Syntax-
RAISERROR (msg_id|msg_str|@local_variable, severity, State ,argument[,..n])
WITH option [,..n]
*msg_id is user defined error message number stored in the sys.messages catalog view. Using sp_addmessage system stored procedure user defined error message can be created in sys.messages table. It should be greater than 50000.When it is not specified RAISERROR raises an error message with an error number 50000.
*msg_str is a user defined message with formatting similar to the printf function in C.
*@local_variable is a variable of any valid charter data type that contains a string formatted in the same manner as msg_str. It must be char or varchar.
*Severity is the user defined severity level associated with this message. It can be 0-18.
* State is an arbitrary integer from 1-127.The value 0 and greater than 127 will raise an error.
Let’s understand this with an example
Problem - you don’t want to divide any number by 2. If anyone tries to divide with 2 it should throw error.
SQL Code-
CREATE PROCEDURE DIVISION_BY_TWO(@FIRST INT, @SECOND INT)
AS
BEGIN
Declare @RESULT int
BEGIN TRY
If @SECOND=2
RAISERROR('I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2',15,1)
SET @RESULT=@FIRST/@SECOND
PRINT 'The Output is: '+ Cast(@RESULT as varchar(2))
END TRY
BEGIN CATCH
PRINT Error_Message()
END CATCH
END
As you can from the above query in try block, if anyone tries to divide by 2 it raise error, catch block just after the try block catch gets the control and it prints the error message written in RAISERROR under TRY block.
Let’s try to divide 20 by 2.
EXEC DIVISION_BY_TWO 20, 2
As you can see from the above result, it raised an error which was raised manually. It printed the error message written in RAISEERROR under TRY block.
If you want to customize the error message with formatting then use the RAISERROR statement as following
RAISERROR ('We don’t want to divide %d with %d',15,1,@FIRST,@SECOND)
We can also use the WITH LOG option at the end of the string to write the error message into the SQL server Log file as following.
RAISERROR (‘We don’t want to divide %d with %d’,15,1,@FIRST,@SECOND) WITH LOG
How to ADD USER Defined Error Messages in sys.messages catalog view?
All the predefined error list of SQL server can be found in the SYS.Messages catalog view. Query on the database with the following statement where we can view the list of predefined errors.
SELECT * FROM SYS.MESSAGES
We can add new user defined error message in SYS.Messages using the system store procedure SP_AddMessage.
Syntax:
SP_AddMessage @msgid, @severity, @msgtext, @lang, @with_log, @replace
Example:
Exec SP_AddMessage 50001,16,'I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2'
You can confirm that above error message has been added into sys.messages table this by running below query
SELECT * FROM SYS.MESSAGES WHERE TEXT LIKE 'I AM RAISING%'
Error Handling
Error Handling (Exception Handling) is one the of key strategy one should think of.
SQL Server 2005 has introduced sophisticated Error Handling approach with TRY-CATCH block which was not available in prior versions.
Here is the approach which can be used as Best practices.
TRY-CATCH BLOCK
In SP, we should put all our logic in TRY block and CATCH block can be utilized for throwing an error
Inside TRY block, we can open a transaction and same can be COMMITed at the end
CATCH block should be used to ROLLBACK a transaction
New in-built functions have been created in SQL Server 2005 like ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE() which are specially for CATCH block.
Every TRY block should be ending CATCH block. 1 TRY can have only 1 corresponding CATCH block
Nesting is allowed means one TRY-CATCH block can be part of outer TRY block.
DESIGN
One Table needs to be created to capture and store all the errors.
One master SP needs to be created which captures the log in above table. This SP will be kept under CATCH block of all the SPs
while using RAISERROR , use WITH LOG. This will log in errorlog also.
DESIGN CODE
CREATE TABLE [ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorDescription] [nvarchar](max) NOT NULL,
[ErrorSourceName] [varchar](255) NOT NULL,
[SysMessagesErrorNumber] [int] NOT NULL,
[ErrorSourceLineNumber] [smallint] NOT NULL,
[IsDeleted] [bit] NOT NULL DEFAULT 0,
[CreatedBy] [int] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETUTCDATE(),
CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)
)
CREATE PROCEDURE [uspThrow_Error]
(
@userProfileId INT=NULL
)
AS
BEGIN
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@errorMessage NVARCHAR(MAX),
@errorNumber INT,
@errorSeverity INT,
@errorState INT,
@errorLine INT,
@errorProcedure NVARCHAR(200)
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@errorNumber = ERROR_NUMBER(),
@errorSeverity = ERROR_SEVERITY(),
@errorState = ERROR_STATE(),
@errorLine = ERROR_LINE(),
@errorMessage = ERROR_MESSAGE();
-- Capturing procedure name along with Schema
SET @errorProcedure =ISNULL(Library.ufnGet_FullObjectName(ERROR_PROCEDURE(),1),'')
-- This is some technical error. So log into ErrorLog table
INSERT INTO Library.ErrorLog
(
SysMessagesErrorNumber, ErrorSourceName,ErrorSourceLineNumber, ErrorDescription, CreatedBy, CreatedDateTime
)
VALUES
(
@errorNumber,@errorProcedure, @errorLine,@errorMessage, ISNULL(@userProfileId,1), GETUTCDATE()
)
-- Now Raise an error. This time it will take Error number as 50000. So Front-end will show a generic Error page.
RAISERROR
(
@errorMessage,
@errorSeverity,
@errorProcedure,
@errorNumber, -- parameter: original error number.
@errorSeverity, -- parameter: original error severity.
@errorState, -- parameter: original error state.
@errorProcedure, -- parameter: original error procedure name.
@errorLine -- parameter: original error line number.
) WITH LOG;
END
GO
SAMPLE CODE
CREATE PROC SampleProc
AS
BEGIN
BEGIN TRY
BEGIN TRAN
-- Your code
SELECT 1/0
COMMIT
END TRY
BEGIN CATCH
EXEC [uspThrow_Error]
END CATCH
END
RAISERROR()
Introduction
Microsoft Error handling is an important factor for every developer. Here in this article, I am not going to explain the entire process of error handling. But here in this article I am trying to explain most important portion of Error handling is called RaisError() function.
Syntax of RaisError()
RAISERROR ( { Message ID | MessageText}
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Setting Custom Error Messages
The user defines error message number stores on sys.messages catalog view. First have a look on the catalog view.
SELECT * FROM sys.sysmessages;
Understanding the Syntax
Here the Error Number is very important. The user defines error numbers begins at greeter then 50000.
We can add the custom error message by using system stored procedure namedsp_AddMessage.
sp_addmessage [ @msgnum= ] msg_id, [ @severity= ] severity ,
[ @msgtext = ' ] 'message'
[, [ @lang = ] 'Language' ]
[, [ @with_log = ] 'log' ]
[, [ @replace = ] 'replace' ]
@msgnum
It indicate the number of Message for user define message it should be grater then 50000
@severity
It means the priority of the Error Messages. It’s range from 0-25. But for user define message we use 0-19. Severity level 20-25 is considered as fatal error.
A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.
Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.
@msggtext
The error message can have a maximum of 2,047 characters. If the message has more than 2,047 characters, then will show only 2,044 characters with an ellipsis to indicate that the message has been truncated
We can pass the optional custom parameters within the message text. This parameter is useful to display any custom messages within the message text. The details of the parameters are mentioned bellow.
Type of Separation
Representation
d or i
Signed integer
O
Unsigned Octal
S
String
U
Unsigned Integer
@lang
It means the language that we want to specify.
@with_log
Set TRUE to log the event in event viewer.
@replace
If the same message number already exists, but we want to replace the string for that ID, we have to use this parameter.
Example with sp_AddMessage
Here we are going to add a custom error message by using sp_AddMessage stored procedure.
EXEC sp_addmessage
@msgnum = 50009,
@severity = 1,
@msgtext = 'Example Custom Error Message';
GO
SELECT * FROM sys.sysmessages where error=50009;
Here we provide an example to understand it properly.
EXEC sp_addmessage
@msgnum = 50010,
@severity = 1,
@msgtext = 'Invalid Sales Order Number : %s for Quantity :%i';
GO
SELECT * FROM sys.sysmessages where error=50010;
error
severity
dlevel
description
msglangid
50010
1
0
Invalid Sales Order Number : %s for Quantity :%i
1033
How to Call RaisError() Function
BEGIN
DECLARE @str_Sorder VARCHAR(max),
@int_Qty INT;
SET @str_Sorder = 'ORDER-1';
SET @int_Qty = 30;
RAISERROR(50010, 1, 1, @str_Sorder, @int_Qty);
END
Invalid Sales Order Number : ORDER-1 for Quantity :30
Msg 50010, Level 1, State 1
Error handling
Error handling is a very important part of T-SQL development life cycle. Before SQL Server 2005 the error handling is quite difficult to maintain.
From SQL Server 2005 Microsoft provide some easier process to handle our T-SQL error properly. In this article we are discussing about the Error handling process of SQL Server.
The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client.
Message number
Each error message has a number. We can find most of the message numbers in the table sysmessages in the master database. But there some special numbers like 0 and 50000 that do not appear there.
Severity level
A number range from 0 to 25. This is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in our SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For system messages we can find the severity level in master ..sysmessages.
State
A number range from 0 to 127. The meaning of this item is specific to the error message, but Microsoft has not documented these values.
Procedure
In which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).
Line
The line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.
Message text
This is the actual text of the message that tells us what went wrong. We can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.
Error handling process in Microsoft SQL Server 2000
As our main concern is to demonstrate the error handling process at MS SQL Server 2005 and above, we are not going to depth of MS SQL Server 2000 error handling process.
@@error
Microsoft SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error. @@error will hold the number of that message.
Now we are trying to generate error and display the error message by @@error global variable.
CREATE TABLE notnull(a int NOT NULL)
DECLARE @err int,
@value int
INSERT notnull VALUES (@value)
SELECT @err = @@error
IF @err <> 0
PRINT '@err is ' + ltrim(str(@err)) + '.'
Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'a', table 'MAHAGUN.dbo.notnull'; column does not allow nulls. INSERT fails.
The statement has been terminated.
@err is 515.
@@rowcount
This is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error we need to save it in a local variable if we want to use the value later, since @@rowcount is set after each statement. Since with SET we can only assign variable at a time, we must use the SELECT if you need to save both @@error and @@rowcount into local variables:
SELECT @err = @@error, @rowc = @@rowcount
@@trancount
This is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless we have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.
Error handling process in Microsoft SQL Server 2005
The Microsoft SQL Server 2005 provides TRY… CATCH mechanism to handle with error. It is so effective and easy to use for T-SQL developer.
The syntax of the blogs is mentioned bellow.
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- TSQL Starements
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Error Handling Code
ROLLBACK TRANSACTION
END CATCH
END
Here in this block of BEGIN TRY and END TRY if any kind of error occurs it directly jump to the BEGIN CATCH and END CATCH block and handle the error.
The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server. Below is a list of the data that can be retrieved when an error occurs.
ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message.
A sample code of calling stored procedure using error handling technique is mentioned bellow.
BEGIN TRY
BEGIN TRY
EXECUTE up_MyProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
EXECUTE up_MyProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
RAISERROR() statement
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.
RAISERROR can be used to:
1. Assign a specific error number, severity and state.
2. Record the error in Windows Application Log.
3. Return messages that contain variable text.
Example:
DECLARE @DBID INT;
SET @DBID = DB_ID();
DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();
RAISERROR
(N'The current database ID is:%d, the database name is: %s.',
10, -- Severity.
1, -- State.
@DBID, -- First substitution argument.
@DBNAME); -- Second substitution argument.
GO
Error handling process in Microsoft SQL Server 2012
In Microsoft SQL server 2012 code named denali in traduced HROW statement.
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.
If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statementcauses the statement batch to be ended.
RAISERROR statement
THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.
The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.
There is no severity parameter. The exception severity is always set to 16.
Example:
-- IN SQL Server 2005
BEGIN TRY
DECLARE @VarToTest INT
SET @VarToTest = 'C'
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ERRORSEVERITY INT
SET @ERRORMESSAGE = ERROR_MESSAGE()
SET @ERRORSEVERITY = ERROR_SEVERITY()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
END CATCH
-- IN SQL Server 2012
BEGIN TRY
DECLARE @VarToTest INT
SET @VarToTest = 'C'
END TRY
BEGIN CATCH
THROW
END CATCH
How to Raise Error Manually in SQL Server?
How to ADD USER Defined Error Messages in sys.messages catalog view?
How to do Error Handling in SQL Server?
When you have a batch of SQL statements and in that batch of SQL Statements if any SQL statement causes error then it throws the error message but it never terminates the execution of program it continues the execution of SQL statements which comes after that SQL Statement which raised error.
Let’s understand the above problem with an example
I am creating a store procedure which divides two numbers and print the result. As you can see from the below query, I am passing two number in the procedure. I am dividing them and storing the result in variable named as @RESULT. Initially I am assigning 0 values for the @RESULT variable and later updating the variable value with the result.
CREATE DATABASE TEACHMESQLSERVER
USE TEACHMESQLSERVER
GO
CREATE PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECOND INT)
AS
BEGIN
DECLARE @RESULT INT
SET @RESULT=0
SET @RESULT=@FIRST/@SECOND
PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)
END
Let’s execute the above procedure
EXEC DIVISION_BY_ZERO 20,10
As you can see from the result below; when we divide 20 by 10 we get 2 and our procedure prints the result as below.
Now let’s try to divide 20 by 0; its universal truth that we can’t divide any number by 0 and we will get error.
EXEC DIVISION_BY_ZERO 20,0
Here you go; we got below error message after executing the above query.
Error message clearly says that divided by zero error encountered. If you have noticed after the error message that Print Statement executed and it printed THE OUTPUT IS: 0. this is wrong right?
It should not have printed this.
To overcome this problem SQL server provides Error handling, when we do error handling the SQL statement which cause the error will be terminated and the execution of stored procedure or batch will also be stopped.
In T-SQL programming error handling is done by using TRY and CATCH construct. Errors in T-SQL code can be processed using a TRY-CATCH construct. A TRY -CATCH construct consists of two parts TRY block and CATCH block. TRY Block contains the SQL statement which may cause error, when an error situation is detected in TYRY block, control is passed to a CATCH block where it can be processed or handled. After the CATCH block handles the exception (Error), control is then transferred to the first T-SQL statement that follows the END CATCH statement. A TRY block always starts with the BEGIN TRY statement and ends with END TRY statement. A CATCH block always starts with a BEGIN CATCH statement and ends with END CATCH statement. A CATCH block must follow a TRY block. In T-SQL each TRY block is associated with only one CATCH block.
Let’s do error handling in above procedure.
ALTER PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECCOND INT)
AS
BEGIN
DECLARE @RESULT INT
SET @RESULT=0
BEGIN TRY
SET @RESULT=@FIRST/@SECCOND
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)
END
As you can see from the above code, we have kept SET @RESULT=@FIRST/@SECOND in TRY Block. It is because this line of SQL statement is creating dividing by zero error. We have CATCH block next to this TRY block. CATCH block is handling the error raised by its TRY block.
Let’s try to divide 20 by 0.
GO
EXEC DIVISION_BY_ZERO 20,0
As you can from the above output, this time we didn't get any error message but we get a message saying Divide by Zero encountered and it also terminates the execution and didn't print THE OUTPUT IS:0 like last time.
How to Raise Error Manually in SQL Server?
We can also raise errors manually at some required situations. It is used to return messages back to application using the same format as a system error or warning message is generated. For raising an error manually we use RAISERROR statement. RAISERROR can return either a user defined error message that has been created using the SP_ADDMESSAGE system stored procedure or a message string specified in the RAISERRORstatement.
Syntax-
RAISERROR (msg_id|msg_str|@local_variable, severity, State ,argument[,..n])
WITH option [,..n]
*msg_id is user defined error message number stored in the sys.messages catalog view. Using sp_addmessage system stored procedure user defined error message can be created in sys.messages table. It should be greater than 50000.When it is not specified RAISERROR raises an error message with an error number 50000.
*msg_str is a user defined message with formatting similar to the printf function in C.
*@local_variable is a variable of any valid charter data type that contains a string formatted in the same manner as msg_str. It must be char or varchar.
*Severity is the user defined severity level associated with this message. It can be 0-18.
* State is an arbitrary integer from 1-127.The value 0 and greater than 127 will raise an error.
Let’s understand this with an example
Problem - you don’t want to divide any number by 2. If anyone tries to divide with 2 it should throw error.
SQL Code-
CREATE PROCEDURE DIVISION_BY_TWO(@FIRST INT, @SECOND INT)
AS
BEGIN
Declare @RESULT int
BEGIN TRY
If @SECOND=2
RAISERROR('I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2',15,1)
SET @RESULT=@FIRST/@SECOND
PRINT 'The Output is: '+ Cast(@RESULT as varchar(2))
END TRY
BEGIN CATCH
PRINT Error_Message()
END CATCH
END
As you can from the above query in try block, if anyone tries to divide by 2 it raise error, catch block just after the try block catch gets the control and it prints the error message written in RAISERROR under TRY block.
Let’s try to divide 20 by 2.
EXEC DIVISION_BY_TWO 20, 2
As you can see from the above result, it raised an error which was raised manually. It printed the error message written in RAISEERROR under TRY block.
If you want to customize the error message with formatting then use the RAISERROR statement as following
RAISERROR ('We don’t want to divide %d with %d',15,1,@FIRST,@SECOND)
We can also use the WITH LOG option at the end of the string to write the error message into the SQL server Log file as following.
RAISERROR (‘We don’t want to divide %d with %d’,15,1,@FIRST,@SECOND) WITH LOG
How to ADD USER Defined Error Messages in sys.messages catalog view?
All the predefined error list of SQL server can be found in the SYS.Messages catalog view. Query on the database with the following statement where we can view the list of predefined errors.
SELECT * FROM SYS.MESSAGES
We can add new user defined error message in SYS.Messages using the system store procedure SP_AddMessage.
Syntax:
SP_AddMessage @msgid, @severity, @msgtext, @lang, @with_log, @replace
Example:
Exec SP_AddMessage 50001,16,'I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2'
You can confirm that above error message has been added into sys.messages table this by running below query
SELECT * FROM SYS.MESSAGES WHERE TEXT LIKE 'I AM RAISING%'
Error Handling
Error Handling (Exception Handling) is one the of key strategy one should think of.
SQL Server 2005 has introduced sophisticated Error Handling approach with TRY-CATCH block which was not available in prior versions.
Here is the approach which can be used as Best practices.
TRY-CATCH BLOCK
In SP, we should put all our logic in TRY block and CATCH block can be utilized for throwing an error
Inside TRY block, we can open a transaction and same can be COMMITed at the end
CATCH block should be used to ROLLBACK a transaction
New in-built functions have been created in SQL Server 2005 like ERROR_NUMBER(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_MESSAGE() which are specially for CATCH block.
Every TRY block should be ending CATCH block. 1 TRY can have only 1 corresponding CATCH block
Nesting is allowed means one TRY-CATCH block can be part of outer TRY block.
DESIGN
One Table needs to be created to capture and store all the errors.
One master SP needs to be created which captures the log in above table. This SP will be kept under CATCH block of all the SPs
while using RAISERROR , use WITH LOG. This will log in errorlog also.
DESIGN CODE
CREATE TABLE [ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorDescription] [nvarchar](max) NOT NULL,
[ErrorSourceName] [varchar](255) NOT NULL,
[SysMessagesErrorNumber] [int] NOT NULL,
[ErrorSourceLineNumber] [smallint] NOT NULL,
[IsDeleted] [bit] NOT NULL DEFAULT 0,
[CreatedBy] [int] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETUTCDATE(),
CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED
(
[ErrorLogID] ASC
)
)
CREATE PROCEDURE [uspThrow_Error]
(
@userProfileId INT=NULL
)
AS
BEGIN
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@errorMessage NVARCHAR(MAX),
@errorNumber INT,
@errorSeverity INT,
@errorState INT,
@errorLine INT,
@errorProcedure NVARCHAR(200)
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@errorNumber = ERROR_NUMBER(),
@errorSeverity = ERROR_SEVERITY(),
@errorState = ERROR_STATE(),
@errorLine = ERROR_LINE(),
@errorMessage = ERROR_MESSAGE();
-- Capturing procedure name along with Schema
SET @errorProcedure =ISNULL(Library.ufnGet_FullObjectName(ERROR_PROCEDURE(),1),'')
-- This is some technical error. So log into ErrorLog table
INSERT INTO Library.ErrorLog
(
SysMessagesErrorNumber, ErrorSourceName,ErrorSourceLineNumber, ErrorDescription, CreatedBy, CreatedDateTime
)
VALUES
(
@errorNumber,@errorProcedure, @errorLine,@errorMessage, ISNULL(@userProfileId,1), GETUTCDATE()
)
-- Now Raise an error. This time it will take Error number as 50000. So Front-end will show a generic Error page.
RAISERROR
(
@errorMessage,
@errorSeverity,
@errorProcedure,
@errorNumber, -- parameter: original error number.
@errorSeverity, -- parameter: original error severity.
@errorState, -- parameter: original error state.
@errorProcedure, -- parameter: original error procedure name.
@errorLine -- parameter: original error line number.
) WITH LOG;
END
GO
SAMPLE CODE
CREATE PROC SampleProc
AS
BEGIN
BEGIN TRY
BEGIN TRAN
-- Your code
SELECT 1/0
COMMIT
END TRY
BEGIN CATCH
EXEC [uspThrow_Error]
END CATCH
END
RAISERROR()
Introduction
Microsoft Error handling is an important factor for every developer. Here in this article, I am not going to explain the entire process of error handling. But here in this article I am trying to explain most important portion of Error handling is called RaisError() function.
Syntax of RaisError()
RAISERROR ( { Message ID | MessageText}
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Setting Custom Error Messages
The user defines error message number stores on sys.messages catalog view. First have a look on the catalog view.
SELECT * FROM sys.sysmessages;
Understanding the Syntax
Here the Error Number is very important. The user defines error numbers begins at greeter then 50000.
We can add the custom error message by using system stored procedure namedsp_AddMessage.
sp_addmessage [ @msgnum= ] msg_id, [ @severity= ] severity ,
[ @msgtext = ' ] 'message'
[, [ @lang = ] 'Language' ]
[, [ @with_log = ] 'log' ]
[, [ @replace = ] 'replace' ]
@msgnum
It indicate the number of Message for user define message it should be grater then 50000
@severity
It means the priority of the Error Messages. It’s range from 0-25. But for user define message we use 0-19. Severity level 20-25 is considered as fatal error.
A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.
Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.
@msggtext
The error message can have a maximum of 2,047 characters. If the message has more than 2,047 characters, then will show only 2,044 characters with an ellipsis to indicate that the message has been truncated
We can pass the optional custom parameters within the message text. This parameter is useful to display any custom messages within the message text. The details of the parameters are mentioned bellow.
Type of Separation
Representation
d or i
Signed integer
O
Unsigned Octal
S
String
U
Unsigned Integer
@lang
It means the language that we want to specify.
@with_log
Set TRUE to log the event in event viewer.
@replace
If the same message number already exists, but we want to replace the string for that ID, we have to use this parameter.
Example with sp_AddMessage
Here we are going to add a custom error message by using sp_AddMessage stored procedure.
EXEC sp_addmessage
@msgnum = 50009,
@severity = 1,
@msgtext = 'Example Custom Error Message';
GO
SELECT * FROM sys.sysmessages where error=50009;
Here we provide an example to understand it properly.
EXEC sp_addmessage
@msgnum = 50010,
@severity = 1,
@msgtext = 'Invalid Sales Order Number : %s for Quantity :%i';
GO
SELECT * FROM sys.sysmessages where error=50010;
error
severity
dlevel
description
msglangid
50010
1
0
Invalid Sales Order Number : %s for Quantity :%i
1033
How to Call RaisError() Function
BEGIN
DECLARE @str_Sorder VARCHAR(max),
@int_Qty INT;
SET @str_Sorder = 'ORDER-1';
SET @int_Qty = 30;
RAISERROR(50010, 1, 1, @str_Sorder, @int_Qty);
END
Invalid Sales Order Number : ORDER-1 for Quantity :30
Msg 50010, Level 1, State 1
Error handling
Error handling is a very important part of T-SQL development life cycle. Before SQL Server 2005 the error handling is quite difficult to maintain.
From SQL Server 2005 Microsoft provide some easier process to handle our T-SQL error properly. In this article we are discussing about the Error handling process of SQL Server.
The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client.
Message number
Each error message has a number. We can find most of the message numbers in the table sysmessages in the master database. But there some special numbers like 0 and 50000 that do not appear there.
Severity level
A number range from 0 to 25. This is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in our SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For system messages we can find the severity level in master ..sysmessages.
State
A number range from 0 to 127. The meaning of this item is specific to the error message, but Microsoft has not documented these values.
Procedure
In which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).
Line
The line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.
Message text
This is the actual text of the message that tells us what went wrong. We can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.
Error handling process in Microsoft SQL Server 2000
As our main concern is to demonstrate the error handling process at MS SQL Server 2005 and above, we are not going to depth of MS SQL Server 2000 error handling process.
@@error
Microsoft SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error. @@error will hold the number of that message.
Now we are trying to generate error and display the error message by @@error global variable.
CREATE TABLE notnull(a int NOT NULL)
DECLARE @err int,
@value int
INSERT notnull VALUES (@value)
SELECT @err = @@error
IF @err <> 0
PRINT '@err is ' + ltrim(str(@err)) + '.'
Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'a', table 'MAHAGUN.dbo.notnull'; column does not allow nulls. INSERT fails.
The statement has been terminated.
@err is 515.
@@rowcount
This is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error we need to save it in a local variable if we want to use the value later, since @@rowcount is set after each statement. Since with SET we can only assign variable at a time, we must use the SELECT if you need to save both @@error and @@rowcount into local variables:
SELECT @err = @@error, @rowc = @@rowcount
@@trancount
This is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless we have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.
Error handling process in Microsoft SQL Server 2005
The Microsoft SQL Server 2005 provides TRY… CATCH mechanism to handle with error. It is so effective and easy to use for T-SQL developer.
The syntax of the blogs is mentioned bellow.
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- TSQL Starements
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Error Handling Code
ROLLBACK TRANSACTION
END CATCH
END
Here in this block of BEGIN TRY and END TRY if any kind of error occurs it directly jump to the BEGIN CATCH and END CATCH block and handle the error.
The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server. Below is a list of the data that can be retrieved when an error occurs.
ERROR_NUMBER() - returns the number of the error.
ERROR_SEVERITY() - returns the severity.
ERROR_STATE() - returns the error state number.
ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() - returns the line number inside the routine that caused the error.
ERROR_MESSAGE() - returns the complete text of the error message.
A sample code of calling stored procedure using error handling technique is mentioned bellow.
BEGIN TRY
BEGIN TRY
EXECUTE up_MyProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
EXECUTE up_MyProc
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity;
END CATCH;
RAISERROR() statement
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.
RAISERROR can be used to:
1. Assign a specific error number, severity and state.
2. Record the error in Windows Application Log.
3. Return messages that contain variable text.
Example:
DECLARE @DBID INT;
SET @DBID = DB_ID();
DECLARE @DBNAME NVARCHAR(128);
SET @DBNAME = DB_NAME();
RAISERROR
(N'The current database ID is:%d, the database name is: %s.',
10, -- Severity.
1, -- State.
@DBID, -- First substitution argument.
@DBNAME); -- Second substitution argument.
GO
Error handling process in Microsoft SQL Server 2012
In Microsoft SQL server 2012 code named denali in traduced HROW statement.
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.
If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statementcauses the statement batch to be ended.
RAISERROR statement
THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.
The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.
There is no severity parameter. The exception severity is always set to 16.
Example:
-- IN SQL Server 2005
BEGIN TRY
DECLARE @VarToTest INT
SET @VarToTest = 'C'
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ERRORSEVERITY INT
SET @ERRORMESSAGE = ERROR_MESSAGE()
SET @ERRORSEVERITY = ERROR_SEVERITY()
RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
END CATCH
-- IN SQL Server 2012
BEGIN TRY
DECLARE @VarToTest INT
SET @VarToTest = 'C'
END TRY
BEGIN CATCH
THROW
END CATCH
