One of the developer at my company thought that we can not use RAISEERROR function in new feature of SQL Server 2005 TRY…CATCH. When asked for explanation he suggested SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling article as excuse suggesting that I did not give example of RAISEERROR with TRY…CATCH. We all thought it was funny. Just to keep record straight, TRY…CATCH can sure use RAISEERROR function.
First read original article for additional information about how TRY…CATCH works with ERROR codes. SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling
Example 1 : Simple TRY…CATCH without RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT 'Divide by zero error encountered.' ErrorMessage
END CATCH;
GO
ResultSet:
ErrorMessage
———————————
Divide by zero error encountered.
Example 2 : Simple TRY…CATCH with RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH;
GO
ResultSet:
Msg 50000, Level 16, State 1, Line 9
Divide by zero error encountered.
A Note on the Spelling of the RAISEERROR Function
One small thing trips up many people. The command is actually spelled RAISERROR, with only one E in the middle. Many of us, me included, write it with the extra E in titles and notes, but SQL Server will not recognize that spelling in code. Type the real one in your scripts.
A few things are good to know when you use it inside a CATCH block:
- The re-raised error comes back as error 50000, as you can see in the second result above. The original error number is lost, so save
ERROR_NUMBER()in your message or in a log table if you need it later. - Pass the real severity and state too. Capture
ERROR_SEVERITY()andERROR_STATE()into variables and use them instead of fixed values. - Inside a TRY block, a RAISERROR with severity 11 to 19 jumps to the CATCH block. Severity 10 or lower is only an informational message and does not.
From SQL Server 2012 onward there is also THROW. A plain THROW inside a CATCH block raises the original error again with its own number, which is often what you want. On SQL Server 2005 and 2008, the pattern in this post is still the way to go. Whatever you choose, keep the message readable, because it is often the only clue the next person gets.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
16 Comments. Leave new
Thanks for the easy to follow reference on using TRY CATCH in Sql Server Pinal Dave. This was exactly what I was looking for.
It is very usefull.This small example explains the new feature in Sql Server 2005
Thanks for help me to get Raise Error Method
I am using SQL 2000. I have a RAISEERROR statement in my SP which cause transaction to be rollback in .net. So when I try to commit the transaction in code, it gives error “The COMMIT TRANSACTIN request has no corresponding BEGIN TRANSACTION.’
Any idea ?
may be you have not written begin tran that why that error is coming
Hi Kshitij,
You need to include The commit Transaction in begin try and end try block.
In Begin catch and end catch block include Rollback transaction.
Please note one subtle but important difference between what you wrote and what the developer tried: the spelling of RAISERROR.
There is only one E in the function, whereas the developer thinks there are two (RAISEERROR).
Hope this explains.
ALso, pls note while giving the range of sysmessages. we need to make sure, we are not providing anything in the system error message range numbers for Raiserror.
i have a problem in inserting data into table.i have given datatype as numeric(5,2) for a column but server is showing error -arithmetic overflow converting numeric datatype to numeric.i am working on sql server.
@mayank
please let us see what script you write for this.
This nice explanation of error handling message
Thanks.
Hi,
Plzz See following Table structure
CREATE TABLE ERRORLOG(
[DATETIME] DATETIME,
USERID VARCHAR(15),
PROCEDURENAME VARCHAR(200),
PARAMETERVALUE VARCHAR(500),
ERRORMSG VARCHAR(200),
ERRORPOSITION VARCHAR(100)
)
in that table structure
i got [DATETIME], my USERID,current PROCEDURENAME, PARAMETERVALUE, ERRORMSG..
but i can not find out
Q. How to fetch ErrorPosition ( ie Line Number) of Current Procedure?????
–> i use ERROR_LINE() in catch block but it is Shown only 1 value for any error is occurred.
if any1 get ans reply immediately.
AWESOME THANKS…………..
I m using Sql 2000 , I Raised the Error in Trigger it is user Defined Error How to Print that Error Message in Stored Procedure
During Insert
RAISERROR (‘BOTH ItemID and SubCompID have value’,16, 1)
I Need to print ‘BOTH ItemID and SubCompID have value’ In Stored Procedure
Can use Try catch in View with SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
Once again .. How can you do that … To do it EASIER …. :) Thank you SO much