SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Handling SQL Server Errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/05/2010 :  08:00:23  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
82 Posts

Posted - 08/04/2010 :  07:56:11  Show Profile  Reply with Quote
In the first example.
A closed Bracket is needed in the column [Second]


Nice article, I have found much value in using Try Catch.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 08/17/2010 :  15:04:51  Show Profile  Reply with Quote
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



Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 08/18/2010 :  05:53:51  Show Profile  Reply with Quote
"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
Go to Top of Page

rrozema
Starting Member

Netherlands
1 Posts

Posted - 08/24/2011 :  05:38:34  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/04/2011 :  02:38:34  Show Profile  Reply with Quote
If there a way to simulate a "doomed" transaction?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 09/06/2011 :  09:45:21  Show Profile  Visit russell's Homepage  Reply with Quote
1/0
Go to Top of Page

deepakvermaseo
Starting Member

India
6 Posts

Posted - 09/07/2011 :  07:32:15  Show Profile  Reply with Quote
first of all the article title should be unique content
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

USA
424 Posts

Posted - 04/12/2012 :  06:57:12  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Go to Top of Page

smh
Yak Posting Veteran

USA
91 Posts

Posted - 07/18/2012 :  15:34:03  Show Profile  Reply with Quote
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
Go to Top of Page

gurjer48
Starting Member

India
1 Posts

Posted - 10/18/2012 :  08:59:52  Show Profile  Reply with Quote
Thank you...super...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000