Hi everybody,
I'm trying to figure out if I'm using a right concept with SP, transactions and error handling.
Comming from VFP background if you want to update multiple tables you usually wrap it in transactions.
Here is what I put right now in the CATCH block in SP, but like to understand if I'm correct :
BEGIN CATCH
DECLARE @ErrorSeverity int, @ErrorNumber int,
@ErrorMessage nvarchar(4000), @ErrorState int,
@ErrorLine int, @ErrorProc nvarchar(200)
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
SET @ErrorLine = ERROR_LINE()
SET @ErrorProc = ERROR_PROCEDURE()
SET @ErrorMessage = 'Problem updating person''s information.' + CHAR(13) +
'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) +
' in procedure: ' + @ErrorProc +
' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +
' Error text: ' + @ErrorMessage
IF @ErrorState = 0
SET @ErrorState = 1
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)
END CATCH
After reading some related articles in BOL the question just seems to be more and more complex to me...
Thanks a lot in advance for the explanations.
If it's not broken, fix it until it is.
My Blog