July 11, 2018 at 2:01 pm
I have the following code
Create procMaster as
Begin try
Begin transaction
exec procChild 123 , 'ABC'
if @@trancount > 0
Commit Transaction
End Try
Begin Catch
if @@trancount > 0
Rollback Transaction
End Catch
Create procedure ProcChild (@Hid int, @Name varchar(50))
as
Begin try
Begin transaction
--all the action steps
if @@TRANCOUNT > 0
Commit Transaction
End Try
BEGIN CATCH
if @@error<> 0 and @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION TRAN1
Declare @ErrorMessage nvarchar(4000),@ErrorSeverity int , @ErrorState int
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState)
end
END CATCH
I need to capture the error from the ProcChild in ProcMaster so that I can log them in a error log table . How can I achieve that?
July 11, 2018 at 3:01 pm
Log errors where they occur. Nesting stored procedures is problematic enough without adding additional complications. Every stored procedure is best served when it entirely takes care of itself. That is occasionally inconvenient, but that usually means someone somewhere messed up the database design.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2018 at 3:06 pm
sgmunson - Wednesday, July 11, 2018 3:01 PMLog errors where they occur. Nesting stored procedures is problematic enough without adding additional complications. Every stored procedure is best served when it entirely takes care of itself. That is occasionally inconvenient, but that usually means someone somewhere messed up the database design.
I did try logging error in the called stored procedure but then the master procedure does not error out when the child proc errors out.
July 11, 2018 at 3:09 pm
Guras - Wednesday, July 11, 2018 3:06 PMsgmunson - Wednesday, July 11, 2018 3:01 PMLog errors where they occur. Nesting stored procedures is problematic enough without adding additional complications. Every stored procedure is best served when it entirely takes care of itself. That is occasionally inconvenient, but that usually means someone somewhere messed up the database design.I did try logging error in the called stored procedure but then the master procedure does not error out when the child proc errors out.
Here is the error logging part in the child proc
BEGIN CATCH
if @@error<> 0 and @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION TRAN1
EXEC Data1..uspErrorHandling @paramterList
end
---then the error log gets rolled back when I call the rollback transaction from the master proc
July 11, 2018 at 3:23 pm
Guras - Wednesday, July 11, 2018 3:09 PMGuras - Wednesday, July 11, 2018 3:06 PMsgmunson - Wednesday, July 11, 2018 3:01 PMLog errors where they occur. Nesting stored procedures is problematic enough without adding additional complications. Every stored procedure is best served when it entirely takes care of itself. That is occasionally inconvenient, but that usually means someone somewhere messed up the database design.I did try logging error in the called stored procedure but then the master procedure does not error out when the child proc errors out.
Here is the error logging part in the child proc
BEGIN CATCH
if @@error<> 0 and @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION TRAN1
EXEC Data1..uspErrorHandling @paramterList
end
---then the error log gets rolled back when I call the rollback transaction from the master proc
You'll have to pass a success or failure value back to the calling stored procedure, and have to capture that value and process it. You could use an OUTPUT parameter in the called stored procedure....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 8:11 am
sgmunson - Wednesday, July 11, 2018 3:23 PMGuras - Wednesday, July 11, 2018 3:09 PMGuras - Wednesday, July 11, 2018 3:06 PMsgmunson - Wednesday, July 11, 2018 3:01 PMLog errors where they occur. Nesting stored procedures is problematic enough without adding additional complications. Every stored procedure is best served when it entirely takes care of itself. That is occasionally inconvenient, but that usually means someone somewhere messed up the database design.I did try logging error in the called stored procedure but then the master procedure does not error out when the child proc errors out.
Here is the error logging part in the child proc
BEGIN CATCH
if @@error<> 0 and @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION TRAN1
EXEC Data1..uspErrorHandling @paramterList
end
---then the error log gets rolled back when I call the rollback transaction from the master proc
You'll have to pass a success or failure value back to the calling stored procedure, and have to capture that value and process it. You could use an OUTPUT parameter in the called stored procedure....
Than you but here , I tried using error message as the output and it works but here is the problem
alter PROCEDURE [dbo].[uspCalled]
@ErrorMessage NVARCHAR(4000) OUTPUT
,@ErrorSeverity INT OUTPUT
,@ErrorState INT OUTPUT
AS
BEGIN TRY
Begin transaction
insert into UserNameTest(username,userid)
select 'John1',614
insert into UserRoleTest(UserRole)
user role is int data type
select 'Manager'
COMMIT transaction
END TRY
BEGIN CATCH
Rollback transaction
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE() ;
EXEC clientData..uspErrorHandling 'uspCalled failed'
END
CATCH
--parent proc
alter
procedure uspCalling
as
declare @ErrorMessage NVARCHAR(4000)
declare @ErrorSeverity INT
declare @ErrorState INT
exec
[dbo].[uspCalled] @ErrorMessage output,@ErrorSeverity output ,@ErrorState output
exec [dbo].[uspCalled] @ErrorMessage output,@ErrorSeverity output ,@ErrorState output
select @ErrorMessage ,@ErrorSeverity, @ErrorState
if @ErrorMessage is not null
RAISERROR('failed',16,1)
GO
--I need the whole transaction to rollback ( from the first call and the second call)
July 12, 2018 at 8:54 am
That's one reason why I never nest stored procedures within a transaction. Too many pieces to go wrong, and too much to keep track of. Extremely difficult to troubleshoot under production pressure when something goes wrong. If you don't use SAVE TRAN xxxx to keep track of where you are, and check for errors after every sub-transaction, things get ugly quickly. It's usually a much less painful process to keep a transaction entirely within one stored procedure. There are folks with far more experience at this than I have, so they may be able to do a better job, but the problem here is still one of design. If you want the entire transaction to roll-back, then you have to check the error output from EVERY sub procedure and act accordingly. It gets complicated and quickly, but that's the nature of using nested stored procedures within a transaction. Most transactions should be things that don't take very long, so the more complicated the overall process becomes, the longer the transaction takes, and in high-volume OLTP environments, that can be bad news in a hurry, as locks are then lasting longer, and creating more opportunities for blocking, and once that starts happening, trouble is nigh...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 12, 2018 at 9:13 am
sgmunson - Wednesday, July 11, 2018 3:23 PMGuras - Wednesday, July 11, 2018 3:09 PMGuras - Wednesday, July 11, 2018 3:06 PMsgmunson - Wednesday, July 11, 2018 3:01 PMLog errors where they occur. Nesting stored procedures is problematic enough without adding additional complications. Every stored procedure is best served when it entirely takes care of itself. That is occasionally inconvenient, but that usually means someone somewhere messed up the database design.I did try logging error in the called stored procedure but then the master procedure does not error out when the child proc errors out.
Here is the error logging part in the child proc
BEGIN CATCH
if @@error<> 0 and @@TRANCOUNT > 0
begin
ROLLBACK TRANSACTION TRAN1
EXEC Data1..uspErrorHandling @paramterList
end
---then the error log gets rolled back when I call the rollback transaction from the master proc
You'll have to pass a success or failure value back to the calling stored procedure, and have to capture that value and process it. You could use an OUTPUT parameter in the called stored procedure....
alter procedure uspCalling
as
declare @ErrorMessage NVARCHAR(4000)
declare @ErrorSeverity INT
declare @ErrorState INT
declare @procName varchar(50) = null
declare @startTrancount int
Begin try
Select @startTrancount = 0
if @startTrancount = 0
begin
Begin Transaction
exec [dbo].[uspCalled2] @ErrorMessage output,@ErrorSeverity output ,@ErrorState output ,@procName output
exec [dbo].[uspCalled1] @ErrorMessage output,@ErrorSeverity output ,@ErrorState output ,@procName output
select @ErrorMessage ,@ErrorSeverity, @ErrorState , @procName
IF @starttrancount = 0
Commit Transaction
end
End Try
Begin catch
if XACT_STATE () <> 0 and @startTrancount = 0
Rollback Transaction
-- RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState)
EXEC Data1..uspErrorHandling @procName
End Catch
GO
The above works,both the procstransactions are rolled back but the uspcalling completes successfully after rollback. It needs to throw error
July 12, 2018 at 11:52 am
I would also be sure you set your error variables BEFORE you roll back your transaction, and I would NOT rely on yet another separate stored procedure to play error handler. It's not that complicated to THROW an error of a high enough level to stop execution, using your error variables. From the appearance of the end of your code, you commented out your RAISERROR and called an error handling sproc instead. Using a stored procedure for absolutely everything is not necessarily a good idea, and is probably a bad idea here.... Do remember that every extra procedure call has overhead, and the more procedures you have, the more overhead there is. For throwing an error, it's not worth it. Just be sure to set the error level high enough to cause execution to stop.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply