SummaryBareGrep as much as anybody, it is a bit like Google. If you don’t enter the right search terms, you still don’t find it.
So this post (and future posts like it) are an attempt to put that code in “one place” where I hope to be able to find it down the road. If the chunk of code is not worth a blog post, then it must not have been that impressive after all. 🙂
This morning, I was trying to to implement error handling in my catch block. I found a chunk of code I had used in the past, and it was rolling back transactions, which reminded me that I had forgot to put that in the current sproc I was working on. At that point, I started this post. Below are some notes that should be helpful to me in the future. I hope they will benefit you as well.
Include some kind of comment. I include a PRINT statement, which helps in the SqlCmd (future post) script that calls this. This also includes the DROP/CREATE approach which I like to do for sprocs.
/* Revision History 4Jul15 ScottB Initial Version */ PRINT 'ProceduresCall_spt.sql' SET ANSI_NULLS ON GO SET ANSI_NULLS ON GO -- http://technet.microsoft.com/en-us/library/ms190324(v=sql.100).aspx IF (OBJECT_ID(N'call.Call_spt', 'P') IS NOT NULL ) BEGIN DROP PROCEDURE call.Call_spt PRINT ' Dropped'; END GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE call.Call_spt @xml XML AS ...
Declare a couple variables at the very beginning that will be needed later. The @tranCount is needed to be able to commit and rollback transactions. The @tenantId is needed in the Error handler, so we set it outside the BEGIN TRY (not sure if this is necessary in TSQL, but it would be in C#).
BEGIN SET NOCOUNT ON; DECLARE @tranCount INT = @@TRANCOUNT; -- Default to System, but it should get reset below. DECLARE @tenantId INT = 1;
BEGIN TRANSACTION ?? / COMMIT
As close as possible to your actual Insert or Update, you need a block like this. It handles the situation where we were already in a transaction (possibly from a calling sproc).
IF (@tranCount = 0) BEGIN BEGIN TRANSACTION END ELSE BEGIN SAVE TRANSACTION Call_spt END -- Insert or Update ... IF (@tranCount = 0) BEGIN COMMIT TRANSACTION END
This next block is assuming that the prior code was in a TRY CATCH block.
BEGIN CATCH DECLARE @xstate INT = XACT_STATE(); IF @xstate = -1 BEGIN ROLLBACK TRANSACTION END IF @xstate = 1 AND @tranCount = 0 BEGIN ROLLBACK TRANSACTION END IF @xstate = 1 AND @tranCount > 0 BEGIN ROLLBACK TRANSACTION Call_spt END PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' + CONVERT(varchar(5), ERROR_LINE()); PRINT ERROR_MESSAGE(); EXEC tenant.ReThrowError_spt @tenantId RETURN -1; END CATCH