Article: Handling SQL Server Errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Handling SQL Server Errors

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2010-04-05 : 08:00:23

This article covers the basics of TRY CATCH error handling in T-SQL introduced in SQL Server 2005. It includes the usage of common functions to return information about the error and using the TRY CATCH block in stored procedures and transactions.

Read Handling SQL Server Errors

acollins74
Yak Posting Veteran

82 Posts

Posted - 2010-08-04 : 07:56:11

In the first example.
A closed Bracket is needed in the column [Second]

Nice article, I have found much value in using Try Catch.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 15:04:51

So the catch needs to immediatley fall after the try?

What about @@ERROR and @@ROWCOUNT and these other system vars

Are the reset immediatley after another execution?

like




BEGIN TRY
    SELECT [Second] = 1/0
END TRY
BEGIN CATCH
	GOTO Sproc_Error
END CATCH

PRINT 'Good Execution...fall through Logic'

Sproc_Exit:

Return

Sproc_Error:

    SELECT [Error_Line] = ERROR_LINE(),
           [Error_Number] = ERROR_NUMBER(),
           [Error_Severity] = ERROR_SEVERITY(),
           [Error_State] = ERROR_STATE()

    SELECT [Error_Message] = ERROR_MESSAGE()
    
GOTO Sproc_Exit

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 05:53:51

"So the catch needs to immediatley fall after the try?"

Not sure I follow your question? BEGIN TRY can be for the whole of the logic in the SProc with one CATCH at the end.

"What about @@ERROR and @@ROWCOUNT and these other system vars"

Available in the CATCH (dunno about @@ROWCOUNT - never managed to simulate a resultset of multiple rows that THEN raised an error!)

Put

SELECT @intRowCount = @@ROWCOUNT, @intErrNo = @@ERROR

as the first statement in the CATCH to preserve the system variables

rrozema
Starting Member

1 Post

Posted - 2011-08-24 : 05:38:34

The catch block on the example would be more robust if you use xact_state() instead of @@trancount. This is because the transaction may, depending on the error that occured, be not only rolled back but it may also be doomed. Attempting a rollback will in such cases result in a new error, obscuring the original error. So the better catch block for the example would be:


BEGIN TRY
   BEGIN TRANSACTION trnName;

   COMMIT TRANSACTION trnName;
END TRY

BEGIN CATCH
    IF XACT_STATE() > 0
      ROLLBACK TRANSACTION trnName;
    ELSE IF XACT_STATE() < 0
      ROLLBACK TRANSACTION;
      
    -- And do some cool error handling
END CATCH

Kristen
Test

22859 Posts

Posted - 2011-09-04 : 02:38:34

If there a way to simulate a "doomed" transaction?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-06 : 09:45:21

1/0

deepakvermaseo
Starting Member

6 Posts

Posted - 2011-09-07 : 07:32:15

first of all the article title should be unique content

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-04-12 : 06:57:12

Thank you for the artical, this is helpful as I am trying to update all my procs to have the try catch error handeling. I do have a question though, you state the try catch can catch errors in other procs called by it. Do these sub procs need to have the try catch also or are we supposed to leave that out? Are we supposed to return anything special or does SQL Magic just happen and it knows all? Thank you

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-18 : 15:34:03

I thought you had to put each query in a separate try/catch. What happens with rollback in the original sample if the second insert fails? Does it rollback the first? How do you roll back multiple actions within the same try/catch/ begin trans, commit, rollback... do you have a sample.

Also, I have been using rowcount to check for concurrency error and my own error code since there are none such for this kind of error -- I believe.

if (@@ROWCOUNT=0)
select @tran_status = -11111

gurjer48
Starting Member

1 Post

Posted - 2012-10-18 : 08:59:52

Thank you...super...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources