crackedcornjimmy crackedcornjimmy - 5 months ago 50
SQL Question

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Commit in wrong place?

Here is a shell of my stored procedure with the necessary parts not omitted:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure --name of sproc
--declare sproc params
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION

--declare a few vars

--declare some table variables

--do some work
IF (--some condition here)
BEGIN
--actually do the work
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SET @error = 'some value cannot be NULL'
RAISERROR(@error, 16, 1)
RETURN @error
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT @error = ERROR_NUMBER()
, @message = ERROR_MESSAGE()
, @severity = ERROR_SEVERITY()
, @state = ERROR_STATE()

RAISERROR(@message, @severity, @state)
RETURN @error
END CATCH
END
GO


I am getting a deadlock error (which is not the subject of this post) in the "--actually do some work" section, and then the "Transaction count..." error is thrown.

Is my COMMIT in the wrong place?

Answer

Move the Begin Transaction above the Begin Try. If the try fails and jumps to the catch, everything initialized in the try falls out of scope. Beginning the transaction outside the scope of the try/catch makes it available to both the try and the catch block.