January 15, 2015 at 3:41 pm
I've inherited a SQL Server database in which the procedures all have try-catch. In all but one case, the catch is:
BEGIN CATCH
DECLARE
@error_number int
, @error_message nvarchar(2048)
, @error_severity int
, @error_state int
, @error_line int;
SELECT
@error_number = ERROR_NUMBER()
, @error_message = ERROR_MESSAGE()
, @error_severity = ERROR_SEVERITY()
, @error_state = ERROR_STATE()
, @error_line = ERROR_LINE();
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END;
RAISERROR('Error %d caught at line %d: %s'
, @error_severity
, @error_state
, @error_number
, @error_line
, @error_message);
END CATCH;
In one case, the procedure is called directly by SQL Agent and it contains additional error-handling in the CATCH clause. I accept that CATCH clause.
The other procedures are called by a .NET application with proper error handling and logging, and by other procedures.
I have a few issues with the catch blocks:
- The procedure calls can be deeply nested. It may be difficult to tell where the error originated.
- The error number is replaced with 50000. If I did want to put catch logic in .NET based on the error number, I couldn't do it.
- The ROLLBACK statement sometimes throws its own error. I know this has happened, but I didn't document it well at the time and wasn't able to reproduce it later. I'm watching for it to happen again.
- The .NET application has full logging capabilities plus additional context for the error (what was the app trying to do?)
- SQL code written for nothing adds risk and bloat.
Transactions are used correctly in the procedures. Most of the time, XACT_ABORT is on. If it's not on, I will set it on when removing the CATCH.
I believe these CATCH clauses are worthless, though they may be a corporate coding standard (I hope not). Am I missing something?
January 15, 2015 at 3:46 pm
The ROLLBACK could indeed cause an error if a transaction wasn't active at the time. Here's how to correct that:
IF XACT_STATE() <> 0
BEGIN
ROLLBACK;
END;
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 15, 2015 at 3:54 pm
Stephanie Giovannini (1/15/2015)
I've inherited a SQL Server database in which the procedures all have try-catch. In all but one case, the catch is:
BEGIN CATCHDECLARE
@error_number int
, @error_message nvarchar(2048)
, @error_severity int
, @error_state int
, @error_line int;
SELECT
@error_number = ERROR_NUMBER()
, @error_message = ERROR_MESSAGE()
, @error_severity = ERROR_SEVERITY()
, @error_state = ERROR_STATE()
, @error_line = ERROR_LINE();
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END;
RAISERROR('Error %d caught at line %d: %s'
, @error_severity
, @error_state
, @error_number
, @error_line
, @error_message);
END CATCH;
In one case, the procedure is called directly by SQL Agent and it contains additional error-handling in the CATCH clause. I accept that CATCH clause.
The other procedures are called by a .NET application with proper error handling and logging, and by other procedures.
I have a few issues with the catch blocks:
- The procedure calls can be deeply nested. It may be difficult to tell where the error originated.
- The error number is replaced with 50000. If I did want to put catch logic in .NET based on the error number, I couldn't do it.
- The ROLLBACK statement sometimes throws its own error. I know this has happened, but I didn't document it well at the time and wasn't able to reproduce it later. I'm watching for it to happen again.
- The .NET application has full logging capabilities plus additional context for the error (what was the app trying to do?)
- SQL code written for nothing adds risk and bloat.
Transactions are used correctly in the procedures. Most of the time, XACT_ABORT is on. If it's not on, I will set it on when removing the CATCH.
I believe these CATCH clauses are worthless, though they may be a corporate coding standard (I hope not). Am I missing something?
In the error message being returned you can add additional information to help pinpoint where the error occurs. You could add the name of the stored procedure.
January 15, 2015 at 5:10 pm
In the error message being returned you can add additional information to help pinpoint where the error occurs. You could add the name of the stored procedure.
Yes, I know how to do this and I left that bit off the initial post. However, I still can't determine what the CATCH clause is doing that can't be done by XACT_ABORT.
January 15, 2015 at 5:37 pm
Using TRY/CATCH blocks not only allows you to capture standard errors that can occur you can also use them to capture user defined errors that under normal processing would not be an error. One, for instance may be returning zero rows in a query. Normally, this would not be an error, but it may be an error in the business process.
January 15, 2015 at 5:56 pm
Lynn Pettis (1/15/2015)
Stephanie Giovannini (1/15/2015)
I've inherited a SQL Server database in which the procedures all have try-catch. In all but one case, the catch is:
BEGIN CATCHDECLARE
@error_number int
, @error_message nvarchar(2048)
, @error_severity int
, @error_state int
, @error_line int;
SELECT
@error_number = ERROR_NUMBER()
, @error_message = ERROR_MESSAGE()
, @error_severity = ERROR_SEVERITY()
, @error_state = ERROR_STATE()
, @error_line = ERROR_LINE();
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END;
RAISERROR('Error %d caught at line %d: %s'
, @error_severity
, @error_state
, @error_number
, @error_line
, @error_message);
END CATCH;
In one case, the procedure is called directly by SQL Agent and it contains additional error-handling in the CATCH clause. I accept that CATCH clause.
The other procedures are called by a .NET application with proper error handling and logging, and by other procedures.
I have a few issues with the catch blocks:
- The procedure calls can be deeply nested. It may be difficult to tell where the error originated.
- The error number is replaced with 50000. If I did want to put catch logic in .NET based on the error number, I couldn't do it.
- The ROLLBACK statement sometimes throws its own error. I know this has happened, but I didn't document it well at the time and wasn't able to reproduce it later. I'm watching for it to happen again.
- The .NET application has full logging capabilities plus additional context for the error (what was the app trying to do?)
- SQL code written for nothing adds risk and bloat.
Transactions are used correctly in the procedures. Most of the time, XACT_ABORT is on. If it's not on, I will set it on when removing the CATCH.
I believe these CATCH clauses are worthless, though they may be a corporate coding standard (I hope not). Am I missing something?
In the error message being returned you can add additional information to help pinpoint where the error occurs. You could add the name of the stored procedure.
http://msdn.microsoft.com/en-us/library/ms188398.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply