SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling With RAISEERROR Function

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

Solarwinds

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)

Solarwinds
,
Previous Post
SQL SERVER – Find Name of The SQL Server Instance
Next Post
SQL SERVER – 2005 – Different Types of Cache Objects

Related Posts

16 Comments. Leave new

  • Patrick Farrell
    October 25, 2007 8:42 am

    Thanks for the easy to follow reference on using TRY CATCH in Sql Server Pinal Dave. This was exactly what I was looking for.

    Reply
  • It is very usefull.This small example explains the new feature in Sql Server 2005

    Reply
  • Thanks for help me to get Raise Error Method

    Reply
  • 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 ?

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Ashish Gilhotra
    March 20, 2010 1:14 pm

    @mayank
    please let us see what script you write for this.

    Reply
  • This nice explanation of error handling message
    Thanks.

    Reply
  • 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.

    Reply
  • AWESOME THANKS…………..

    Reply
  • 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

    Reply
  • Dinesh Vishe
    June 22, 2012 7:27 pm

    Can use Try catch in View with SET ARITHABORT OFF
    SET ANSI_WARNINGS OFF

    Reply
  • Once again .. How can you do that … To do it EASIER …. :) Thank you SO much

    Reply

Leave a Reply

Menu