May 8, 2008 at 10:33 am
The downsides of TRY...CATCH are:
1. You cannot re-raise the original exception after examining/handling it in the CATCH block. Contrast with Oracle's PL/SQL exception handling where you can re-raise the original exception via the RAISE statement. In SQL Server 2005 you have to use RAISERROR and put the details of the original exception into the message part of your own RAISERROR text. This may cause problems with the calling application if it is looking for specific SQL Server error (return) codes. The application will have to see the RAISERROR code and then parse the text for the details.
2. At a minimum you have to code something in the CATCH block -- usually re-throwing the error via RAISERROR otherwise the errors go into the "black hole" and you don't know what is wrong. I'll contrast again with Oracle's method whereby you only have to code for the exceptions that you need to anticipate. All others are automatically re-raised to the caller be it another PL/SQL procedure or the source application.
To sum up: Although SQL Server's TRY... CATCH is nice (and long overdue), it is no panacea and still requires a bit of code (and careful thought). I wish that they (MS) had put just a bit more effort into it.
May 8, 2008 at 1:24 pm
riga1966 (5/7/2008)
We don't have "Common" database.Should I create one to store common objects?
That's what I do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 8, 2008 at 4:23 pm
GSquared,
Can you give me an idea what the SP dbo.Error_CatchCommon contains?
Thanks,
Harley
May 9, 2008 at 12:40 pm
The error proc's contents really depends on what you need for your particular application and shop.
The first thing to do is capture the error data. I use variables for that, declared as output parameters in the proc.
After that, what you do depends on what you need.
For example, the proc can have code in it for raising alerts (send e-mail to an an account, or send a message to a cell phone or pager), it can log various things (either in tables or in error logs), it can check for junk data left in tables, it can even raise further errors.
It's really up to you what you need and how much you want to put into the common proc.
I simply found that I was having to type the same pieces of error handling code over and over and over again, in just about every catch block. For me, that started with something to select the error severity, message and line of code. So I put that in a proc so I didn't have to write it again every time.
Here's how it started out:
use Common
go
create proc [dbo].[ErrorCatch2]
/*
Common error handling code. Anything that most/all procs need in Catch
blocks can be done here. Specific handlings for the proc in question should
be kept in that proc (modularity).
*/
(@Nbr_out int = null out,
@Msg_out varchar(2048) = null out,
@Severity_out tinyint = null out,
@State_out int = null out,
@Line_out int = null out,
@Procedure_out varchar(126) = null out)
as
/*
From BOL:
ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message.
The text includes the values supplied for any substitutable parameters
such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the
error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger
where the error occurred.
*/
select @nbr_out = error_number(),
@msg_out = error_message(),
@severity_out = error_severity(),
@state_out = error_state(),
@line_out = error_line(),
@procedure_out = error_procedure()
if user = 'dbo'
select @nbr_out as ErrNum, @msg_out as ErrMsg, @severity_out as ErrSvrty,
@state_out as ErrState, @line_out as ErrLine, @procedure_out as ErrProc
else
begin
--... alerts
--... logging actions
--... whatever else you need here
end
(I replaced most of the specific actions because they contain personal e-mail addresses and cell phone numbers, and so on.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 9, 2008 at 12:49 pm
Thanks for the sample code and excellent explanation.
Regards,
Harley
May 9, 2008 at 1:45 pm
Along the same lines, you may need a "rethrow error" procedure like the following or you can put the "rethrow" logic into the previously suggested procedure.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE usp_RethrowError
-- Purpose:
-- Raises a generic database user exception (RAISERROR) with the values
-- contained in the "ERROR_" variables.
--
-- Input parameters:
-- none
--
-- Output parameters:
-- none
--
-- Returned Result Sets:
-- none
--
-- Technical Note:
-- The ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE,
-- ERROR_SEVERITY, and ERROR_STATE functions only return error
-- information when they are used within the scope of the CATCH block
-- of a TRY...CATCH construct. Outside the scope of a CATCH block
-- they return NULL. These functions return information about the
-- error that caused the CATCH block to be invoked. The functions
-- return the same error information anywhere they are run within
-- the scope of a CATCH block, even if they are referenced multiple
-- times.
-- ***************************************************************************
AS
SET NOCOUNT ON; -- Don't return counts of rows affected.
DECLARE @Error_Message NVARCHAR(4000);
DECLARE @Error_Number INT;
DECLARE @Error_Severity INT;
DECLARE @Error_State INT;
DECLARE @Error_Line INT;
DECLARE @Error_Procedure NVARCHAR(126);
DECLARE @Error_String NVARCHAR(4000);
BEGIN;
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SET @Error_Message = ERROR_MESSAGE();
SET @Error_Number = ERROR_NUMBER();
SET @Error_Severity = ERROR_SEVERITY();
SET @Error_State = ERROR_STATE();
SET @Error_Line = ERROR_LINE();
SET @Error_Procedure = ISNULL(ERROR_PROCEDURE(), N'-');
-- Build the message string that will contain original
-- error information.
SET @Error_String = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: %s';
-- Raise an error. The msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@Error_String,
@Error_Severity,
1,
@Error_Number, -- parameter: original error number.
@Error_Severity, -- parameter: original error severity.
@Error_State, -- parameter: original error state.
@Error_Procedure, -- parameter: original error procedure name.
@Error_Line, -- parameter: original error line number.
@Error_Message -- parameter: original error test.
);
END;
GO
Interesting, that posting a paren followed by a semicolon (as all proper SQL Server T-SQL should have) even inside of the "code" tags results in a smilie (message icon)!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply