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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
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