Nested Try...Catch

Question

Post reply

Nested Try...Catch

charles evans

SSCommitted

Points: 1865
More actions
March 11, 2008 at 1:48 pm

#179975

I so far haven't found any help on the web that would indicate the cause of this. Essentially, the sp I run alway passes an error to the second Catch block in this, even thought the error is encountered in the first Try block. Has anyone had experience with this? Any help would be appreciated. This only happens in this one SP and I have others that use nested try...catch blocks, so I think it must be something I am overlooking.
I am running this on Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) .
Here is the SP:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lsp_ImportDH]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[lsp_ImportDH]
GO
CREATE PROCEDURE [dbo].[lsp_ImportDH]
-- Add the parameters for the stored procedure here
@xml xml = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @idoc int
begin try
begin tran a1
declare @map table
(
HID int not null,
DN int null
primary key (HID)
)
exec sp_xml_preparedocument @idoc OUTPUT, @xml
--populate a temporary table with the data from the xml
insert @map
(
HID,
DN
)
select
HID,
DN
from OPENXML (@idoc, '/COMPANY', 2)
with
(
HIDint 'HID',
DN int 'DN'
)
exec sp_xml_removedocument @idoc
insert tblDH
(
DN,
HID,
LastImport
)
select
DN,
HID,
getdate()
from @map m left join tblDH dh
on m.HID = dh.HID
where dh.HID is null
commit tran a1
return 1
end try
begin catch
begin try
begin tran a2
insert LAA.dbo.tblImportHID2DN_Errors
(
ErrorDate,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage
)
select
getdate(),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
commit tran a2
end try
begin catch
rollback tran a2
end catch
exec sp_xml_removedocument @idoc
rollback tran a1
return 0
end catch
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

charles evans SSCommitted Points: 1865 More actions · Answer 1

Just checking to make sure I receive any replies to this thread via email.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

My first question would be - how are you sure that the second catch is firing?

According to the way you've got it written, the second TRY/CATCH express is enclosed inside TRANS1, which you roll back (which means trans2 rolls back since it's nested).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I wasn't very specific before. You need to move the

rollback tran a1

to be your first statement in the catch. everything prior to that is part of Tran a1, and therefore will be rolled back. That currently includes your error logging TRY...CATCH, meaning - nothing gets logged.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

charles evans SSCommitted Points: 1865 More actions · Answer 4

I get it. I knew it had to be something small that I was overlooking. Thanks!

charles evans SSCommitted Points: 1865 More actions · Answer 5

Oh and I really didn't know for certain where the error was happening; I was just making an assumption.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Fair enough. It took me a little staring at it to see an issue and make a guess that that was it.

glad I "got it right"....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 7

If visual studio is available, you could always use it to debug the SP. My 2 cents.

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

charles evans SSCommitted Points: 1865 More actions · Answer 8

Thanks, I guess I could do a local build of the dev database on my box and set up remote sp debugging on it (we don't want to open things up enough allow the remote debugger on our servers). Thanks for the idea.