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

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 (http://blog.SQLAuthority.com)

About these ads

18 thoughts on “SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling With RAISEERROR Function

  1. 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 ?

    Like

  2. 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.

    Like

  3. 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.

    Like

  4. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 7 Journey to SQL Authority with Pinal Dave

  5. 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.

    Like

  6. 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.

    Like

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

    Like

  8. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 17 of 31 Journey to SQLAuthority

  9. 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

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s