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

SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can focus more on IT business strategy development and less on what needs to happen when errors occur. In SQL Server 2005, @@ERROR variable is no longer needed after every statement executed, as was the case in SQL Server 2000. SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.

Functions to be used in CATCH block are :

  • ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
  • ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
  • ERROR_STATE: returns the state number of the error.
  • ERROR_LINE: returns the line number where the error occurred.
  • ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
  • ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.

Syntax:
BEGIN TRY
{ sql_statement
|
statement_block }
END TRY
BEGIN CATCH
{ sql_statement
|
statement_block }
END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Example of TRY…CATCH:
BEGIN TRY
DECLARE @X INT
---- Divide by zero to generate Error
SET @X = 1/0
PRINT 'Command after error in TRY block'
END TRY
BEGIN CATCH
PRINT 'Error Detected'
END CATCH
PRINT 'Command after TRY/CATCH blocks'

Above code will return following result:

Error Detected
Command after TRY/CATCH blocks


If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.

Limitation of TRY…CATCH:

  • Compiled errors are not caught.
  • Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
  • Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.

For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.

Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks;
GO
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
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;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

51 thoughts on “SQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling

  1. We have found the try/catch functionality extremeley useful, but now I seem to be stuck into a tricky situation with nested cursors.

    What if there is an error in crsSub1 or crsSub2?
    Where shall I declare, open, close and deallocate cursors when I don´t know where an error might occur??

    I cannot close a cursor “on chance”, not knowing if it exists (will cause runtime error) and the same counts for opening a cursor “on chance”.

    Shall I use local variables to keep track of what cursors that are open or is there built in functions that I can use??

    Example:

    declare crsMain local for select Pid from Person
    open crsMain

    while (1=1)
    begin
    fetch crsMain into @Pid

    begin tran
    begin try

    — Nested subcursor Sub1
    declare crsSub1 local for select * from Sub1
    where Pid = @Pid
    open crsSub1

    insert…
    update…
    delete…

    close crsSub1
    deallocate crsSub1

    — Nested subcursor Sub2
    declare crsSub2 local for select * from Sub2
    where Pid = @Pid
    open crsSub2

    insert…
    update…
    delete…

    close crsSub2
    deallocate crsSub2

    commit
    end try

    begin catch
    rollback
    — do some error logging…
    end catch

    end

    close crsMain
    deallocate crsMain

  2. Hi All,

    You Can use the Cursor_Status() function to check the status of the cursor in the CATCH Block and write the code accordingly

    For full detail go to bookonline topic
    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/3a4a840e-04f8-43bd-aada-35d78c3cb6b0.htm

  3. This is a catch block that I created that takes care of a cursor called “file_cursor”. probably could be a little more robust, but it does the trick:

    BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);

    IF @@TRANCOUNT > 0
    BEGIN
    ROLLBACK TRANSACTION;
    PRINT ‘TRANSACTION ABORTED’
    END

    PRINT CURSOR_STATUS(‘global’, ‘file_cursor’)

    IF CURSOR_STATUS(‘global’,’file_cursor’) > 0
    BEGIN
    CLOSE file_cursor
    DEALLOCATE file_cursor
    PRINT ‘The cursor has been closed and deallocated’
    END

    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

    IF @ErrorState < 1
    SET @ErrorState = 1

    — Use RAISERROR inside the CATCH block to return error
    — information about the original error that caused
    — execution to jump to the CATCH block.
    –SET @ErrorMessage = ‘Procedure BLAH: ‘ + @ErrorMessage
    RAISERROR (@ErrorMessage, — Message text.
    @ErrorSeverity, — Severity.
    @ErrorState — State.
    );
    END CATCH

  4. Pingback: SQL SERVER - 2005 - Explanation of TRY…CATCH and ERROR Handling With RAISEERROR Function Journey to SQL Authority with Pinal Dave

  5. your article was extremely useful for my sql dba telephonic interview. I need answers for few questions where i was not sure.

    1. How will you detect that a deadlock occured in SQL server 2000 and how will you resolve it ?
    2.How many stored procedures can be written in a single crystal report?

  6. Hi,

    I want to use 2 try blocks in same stored proc. If the FIRST try block fails it goes to catch..suppose.. if my SECOND block fails, whether the first TRY block transaction gets rolledback or not?
    this is my scenario
    // success
    begin tran
    begin try

    insert1
    insert2
    insert3
    end try

    begin catch
    rollback
    end catch

    end try
    commit tran
    //failure
    begin tran
    begin try

    insert1
    insert2
    insert3
    end try

    begin catch
    rollback
    end catch

    end try
    commit tran

  7. Hi,

    I want to use try-catch block against any user defined condition. say I am ising an IF block to satisfy some conditions . If it will dissatisfy, then I want to go to CATCH block. Is it possible?

    BEGIN TRY
    IF (@variable between 1 AND 8) –condition as per client emand) –
    error produced
    END TRY

    BEGIN CATCH

    END CATCH

  8. I am trying to write the exception details in the text file.

    What will be the faster way ?

    Kamleshkumar Gujarathi.

  9. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  10. Hi,

    If I try to drop a constraint which does not exist in the database by using the below command I get the error message

    Msg 3728, Level 16, State 1, Line 1
    ‘FK_ReferencedWorkspace_SchemaVersion1′ is not a constraint.
    Msg 3727, Level 16, State 0, Line 1
    Could not drop constraint. See previous errors.

    However if I have the same code enclosed within a try .. catch block with the statement

    select ERROR_NUMBER() as ErrorNumber,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() as ErrorMessage

    it only returns me the second error as

    “Could not drop constraint. See previous errors.”

    How do I get the full error message so that I can trobleshoot easily OR is this a limitation of SQL Server 2005

    Thanks
    Ryan

  11. I have a problem in SQL 2000 server because I need something like try .. catch.

    The problem is.

    SELECT tic.cod_record_poliza,tic.cod_ramo
    FROM tISO_Claim tic
    WHERE cod_record_poliza = 99

    Error:
    Server: Msg 245, Level 16, State 1, Line 1
    Syntax error converting the varchar value ‘UP01′ to a column of data type int.

    Note: This error is in a select statement because the column name cod_record_poliza is varchar and in this case the values is 99 instead of ’99′.

    —>>> I need to detect this error. <<<<——-
    The question is: How could I resolve this error?

  12. Hi Perez,

    That is not an issue with Select. this issue with “WHERE”.

    You can write the query as follows:

    SELECT tic.cod_record_poliza,tic.cod_ramo
    FROM tISO_Claim tic
    WHERE cod_record_poliza = ’99′

    Let me know if it helps you.

    Thanks,

    Tejas

  13. Hi All,

    I have a situation in my Trigger.
    My trigger is for update on a Table, whenever there is an update it has to fire the trigger and place the data in 2 Servers one is local and another is Remote server. what i need is sometimes my remote server goes offline, thogh the trigger firing and and i am missing some data. what i want is if is there any problem with servers or DB still it has to fire the trigger and it should notify me with a mail that there was a problem with remote server or DB, but still it has to do all the tasks it is suppose to do. so implemented try catch bolck in catch block i wrote like this

    END TRY

    Begin Catch
    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode 0) GOTO PROBLEM

    End Catch

    PROBLEM:
    IF (@intErrorCode 0)
    BEGIN
    declare @body1 varchar(100)
    set @body1 = ‘Server :’+@@servername+ ‘Error Message:’+ERROR_MESSAGE()
    EXEC msdb.dbo.sp_send_dbmail @recipients=’mailid’,
    @subject = ‘My Mail Test’,
    @body = @body1,
    @body_format = ‘HTML’ ;
    END

    while i am trying get some exception trigger is not getting fired i have to rollback my operation.
    Is there anyway i canget fired my trigger though there is an exception but notifying me with a mail. Any Help….

  14. @Reddy,

    Instead of waiting for trigger to execute SQL statement on remote sql server (which is offline) and fail and then send an email… why don’t you simple check if remote server or remote database is online. If it is online perform action, if it not online, then send email. something like this.

    Inside trigger you can add a check like this,

    if (condition to check if remote server database is online)
    begin
    perform what ever your action you want to perform.
    end

    else
    begin
    xp_sendemail……
    end

    This will definitely not rollback your transaction.

    If you need more help let me know. Also provide details if you are using linked server to connect to remote server.

    ~ IM.

  15. Imran

    Thanks for your quick reply.

    yes i am using linked server. I implemented sqlmail on my local server and i am getting mails.
    As i mplemented Try Catch in my trigger and we know that not all the errors will be cathed in the catch block. so better i implement the the way you suggested.

    How do we check that remote server is online or not, is there any code snippet you have

    part1: if (condition to check if remote server database is online)
    begin
    perform what ever your action you want to perform.
    end

    part2: else
    begin
    xp_sendemail……
    end

    I am okay with the 2nd part and what should i write for part1? is there any system stored procedure to do that in sql2k5 as i am using sql2k5. Reply will be appreciated.

    Thanks in advance.

  16. Hi,

    I would like to print the query I have written inside the SP while executing it so that I can debug it easily
    e.g

    BEGIN
    DECLARE @SQLQUERY varchar(8000)
    SET @SQLQUERY = ‘select * from Roles where Role=1 ‘
    PRINT ‘%1!’, @SQLQUERY
    EXEC(@SQLQUERY)
    END

    I want if I execute it in sql plus it shoul give me results as well as print the query for my knowldege.

    The baove syntaxt works fine with sybase but in sql server it gives me error nea comma in print statement.

  17. Manisha
    What are you trying to print? This print statement will give error. Do you want to concatenate %1 with statemetn in @SQLQUERY.
    If yes then Write
    PRINT ‘%1!’ + @SQLQUERY

  18. Iam using openrowset function to load data from text file.
    First row, first field value has carriage return and hence when
    openrowset function is executed outside the Try – Catch block
    gives the following error.
    Msg 4864, Level 16, State 1, Line 1
    “Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (column_name). ”

    When openrowset is executed inside the Try block, works fine
    , skips the first row as it had problem with the first column value and inserts from second row onwards.
    Basically function inserted all rows excluding the problematic ones, without giving any error.
    How can i find those problematic rows, as none of the errors are caught in Catch Block.

  19. Hi,

    I tried to insert data into a table which doesn’t exists from a SP. In this case, I am not able to trap the error in CATCH block and it breaks while running in between.

    BEGIN TRY
    –Insert statement
    END TRY
    BEGIN CATCH
    PRINT ‘Error Detected’
    END CATCH

    Output:
    Msg 208, Level 16, State 1, Procedure usp_Calculation, Line 805
    Invalid object name ‘tbl_Allocation_Debug’.

  20. Check upfront if object exists or not.

    If exists(
    Select * from information_schema.tables where table_name=
    ) … then go ahead inserting data.

  21. Hi ,

    kindly let me know how to capture the exception raised by the below sql and i want to use insert Try and Catch in the sql below.

    DECLARE @triprequest varchar (50)
    DECLARE @xml_str varchar(4000)
    DECLARE @hdoc int
    declare @temp_table_travel_Req_xml_det table ([triprequest] varchar (50),[name] varchar (50), [created_by] varchar (50), [action] varchar (16), [created_on] datetime)
    DECLARE xml_cur cursor for
    SELECT CMTRAVELCOMMENTS.tripRequest triprequest, cast(cast(CMTRAVELCOMMENTS.config as
    varbinary(8000)) as VARCHAR(8000)) xml_str
    FROM CMTRAVELCOMMENTS, PCMTRAVELREQUEST
    WHERE
    PCMTRAVELREQUEST.NODESTATE = ‘Approved request’
    and CMTRAVELCOMMENTS.tripRequest=PCMTRAVELREQUEST.tripRequest
    OPEN xml_cur
    FETCH xml_cur into @triprequest,@xml_str
    WHILE(@@fetch_status=0)
    BEGIN
    EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml_str
    INSERT into temp_table_travel_Req_xml_det
    SELECT @triprequest,*
    FROM OPENXML (@hdoc,’/cm-comments/comments’, 3)
    WITH ([name] varchar (50), [created_by] varchar (50), [action] varchar (16), [created_on] datetime)
    EXEC sp_xml_removedocument @hdoc
    print @triprequest
    FETCH xml_cur into @triprequest,@xml_str
    END
    CLOSE xml_cur
    DEALLOCATE xml_cur

  22. Hello Sir,

    Is there any way to catch error with severity less than 10.
    I have a Stored Proc wherein dynamic sql query is generated. If the query is wrong, How can i catch the exception?

    If the query generated can be wrong, than the user input is wrong and hence i need to update another table.

    Can u pls help ..

    Thanks
    Geervani

  23. Is it possible for try..catch to be global for some reason? We are seeing stored procedures calling an error statement even though there is no error checking (try catch or otherwise) on that particular statement. For example, in the sp we might do inserts and on those we check @@error and we always do an insert into table called errors if we see a failure, but we have some update statements that we expect to not find anything to update in certain conditions so we do not check for any errors yet when they do not work we are getting an error on not being able to insert into the table errors and the sp bombs. Is it possible for there to be a global try catch that gets called somehow?

  24. I have a stored procedure that updates a linked server. The linked server is returning an error (because the update would violate a business rule).

    ERROR_MESSAGE() has ‘Cannot fetch a row from OLE DB provider “” for linked server “”.’

    This is OK as far as it goes. But when I debug this in VS 2008, the Debug Window displays this much more detailed info:

    OLE DB provider “” for linked server “” returned message “Cursor fetch row failed.
    exception 98
    VER2021-Service Records can not overlap for DSP.”.

    How can I access that more detailed message within my stored procedure’s CATCH block?

  25. i want to know in sql script what type of
    exception are generate like FOREGIN KEY,PRIMARY KEY, UNIQUE KEY VIOLATION
    so that i take a step to generate a appropriate message to
    user that it understand what is error.

  26. Pinal,

    Can you please provide some more info on NESTED TRY CATCH blocks and how the control passes from one to another during calling one stored procedure from another.

    I have the following situation

    CREATE PROCEDURE sp_a(
    ….)
    AS
    BEGIN…
    ..
    BEGIN TRY — Outer Try block
    UPDATE TABLE a…..WHERE …

    EXEC sp_b

    UPDATE TABLE….
    SET a…..
    WHERE….

    END TRY
    BEGIN CATCH

    SET @ErrorMsg = ‘ErrNo: ‘ + ERROR_NUMBER() + ‘ Msg: ‘ +
    ERROR_MESSAGE()

    END CATCH

    END

    —- End of Stored Proc – sp_a

    CREATE PROCEDURE sp_b(..
    …)

    BEGIN TRY — Nested Try Block – Inner block
    INSERT INTO TABLE…..
    VALUES(…)
    END TRY
    BEGIN CATCH

    SET @ErrorMsg = ‘ErrNo: ‘ + ERROR_NUMBER() + ‘ Msg: ‘ +
    ERROR_MESSAGE()
    END CATCH

    END —

    —- End of Stored Proc sp_b

    So in this case if the sproc sp_b returns error, does the stored procedure capture that error and goes to the Outer CATCH block or do I have log the Inner CATCH error separately into a table.

    Any feedback would be much appreciated

  27. I understand the Try Catch, but how can I catch that the link server is down, and test it without taking down the link server?

    BEGIN TRY

    –SELECT 1/0
    SELECT state_desc FROM LinkedServerName.master.sys.databases WHERE [Name] = ‘DBaseName’

    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

  28. Hi,

    I am using sqlserver 2005.if i execute following lines of code in sqlserver catch bolck not executing.Since i dont have Permanenttable and Temporarytable in my db.

    BEGIN TRY
    insert Permanenttable
    select * from Temporarytable
    END TRY
    BEGIN CATCH
    print ‘catch fired’
    END CATCH

  29. I understand try catch blocks having some limitation.
    But how can i handle this type of exception?
    I am having scenario like following

    Create procedure sp1
    as
    Begin

    Begin Try
    Begin Tran

    Declare cursor1 cursor for …………
    ………………
    While @@Fetch_status=0
    Begin

    Declare cursor2 cursor for …………
    ………………
    While @@Fetch_status=0
    Begin

    Declare cursor3 cursor for …………
    ………………
    While @@Fetch_status=0
    Begin

    insert Permanenttable
    select * from Temporarytable
    End
    End

    End

    IF @@TRANCOUNT > 0
    COMMIT TRAN
    End Try

    Begin catch

    Rollback tran

    IF CURSOR_STATUS(‘GLOBAL’, ‘Cursor1′) >= 0
    BEGIN
    CLOSE Cursor1
    DEALLOCATE Cursor1
    END
    IF CURSOR_STATUS(‘GLOBAL’, ‘Cursor2′) >= 0
    BEGIN
    CLOSE Cursor2
    DEALLOCATE Cursor2
    END

    IF CURSOR_STATUS(‘GLOBAL’, ‘Cursor3′) >= 0
    BEGIN
    CLOSE Cursor3
    DEALLOCATE Cursor3
    END
    End catch

    END

    If exception raised at the time of inserting then it will terminate.But my all cursors will be open state only.

    If next time i execute this SP it will give Cursor1 already exist and transaction opened error.

    So can any one tell me how can i acheive this type of scenario.

  30. ALTER PROC SAMPLE1
    AS
    BEGIN
    BEGIN TRY
    PRINT ’1′
    DECLARE @SQL VARCHAR(MAX)
    SET @SQL = ‘insert Permanenttable select * from Temporarytable’
    EXEC(@SQL)
    – select 1/0
    PRINT ’2′
    END TRY
    BEGIN CATCH
    print ‘catch fired’
    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
    END

  31. The best way to test for a linked server is to use SQL’s built in system procedure:

    Ex: EXEC sys.sp_testlinkedserver ‘yourlinkedserver’

  32. Pingback: SQL SERVER – T-SQL Errors and Reactions – SQL in Sixty Seconds #002 – Video « SQL Server Journey with SQL Authority

  33. Pingback: SQL SERVER – T-SQL Errors and Reactions – Demo – SQL in Sixty Seconds #005 – Video « SQL Server Journey with SQL Authority

  34. Hi….. Pinal

    i m new to sql server, so i wanted to know that how can i return the error code using error_number() and error message using error_message() with variable to the calling…………. like we can return in oracle using sqlcode, sqlerrm

  35. Hi… Pinal

    I have been following your blog and failed to understand why the stored procedure gets printed “NULL” when it goes to catch. It is returning the stored procedure name only when there is foreign key relationship violation. or else every time it is “NULL” Could you please help me .

  36. Pingback: SQL SERVER – Weekly Series – Memory Lane – #024 | SQL Server Journey with SQL Authority

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