CODEWITHSUNDEEP
sqlsql-serverstored-procedures
MichaelJ
MichaelJ

Reputation: 49

Stored Procedure Error - Transaction Count mismatch

I've been doing this stored procedure, however when I execute the stored procedure, I get an infinity execution. This cause a deadlock.

This is the error I got, can someone please help me on this?? Thanks.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

Code:

ALTER PROCEDURE [dbo].[spMaterialReceivingCreateItemRequirements]
   @DomainSite nvarchar(8),
   @ItemNo nvarchar(18),
   @tReceiving_id integer,
   @SampleRequired integer,
   @UserName nvarchar(50)
AS
BEGIN
   Declare @ErrorNo integer = '',
           @New_JobNo integer,
           @Status nvarchar(50) = 'InProcess',
           @SPName nvarchar(max) = '',
           @intSampleNo integer =1,
           @ErrorMessage nvarchar(max) = ''

    begin transaction t1
    Begin try
        BEGIN
            --Generate 1 sample for item requirements
            set @SampleRequired = 1

            WHILE (@intSampleNo <= @SampleRequired)
            BEGIN
                insert into tItemRequirements
                   select 
                       domainSite, @tReceiving_id, @ItemNo,
                       WorkCenter, tStationsType_id,
                       tSpecTestParameters_descriptions_id,
                       --row_number() OVER (ORDER BY ID) AS CurrentSet,
                       1 AS CurrentSet,
                       @intSampleNo, 1, 'InComplete', getdate(), @UserName 
                   from 
                       tspectestparameters
                   where 
                       itemno = @ItemNo 

                set @intSampleNo = @intSampleNo +1
            end
        END
        END TRY
        Begin catch
             SELECT 
                 @ErrorNo = ERROR_NUMBER(),
                 @SPName = ERROR_PROCEDURE(),
                 @ErrorMessage = ERROR_MESSAGE();

                 rollback transaction t1
        end catch
END

Upvotes: 0

Views: 1365

Answers (1)

Tristan
Tristan

Reputation: 1024

BEGIN TRANSACTION t1

BEGIN TRY
    BEGIN
        --Generate 1 sample for item requirements
        SET @SampleRequired = 1

        WHILE (@intSampleNo <= @SampleRequired)
        BEGIN
            INSERT INTO tItemRequirements
            SELECT domainSite
                , @tReceiving_id
                , @ItemNo
                , WorkCenter
                , tStationsType_id
                , tSpecTestParameters_descriptions_id
                ,
                --row_number() OVER (ORDER BY ID) AS CurrentSet,
                1 AS CurrentSet
                , @intSampleNo
                , 1
                , 'InComplete'
                , getdate()
                , @UserName
            FROM tspectestparameters
            WHERE itemno = @ItemNo

            SET @intSampleNo = @intSampleNo + 1
        END
    END
COMMIT
END TRY

BEGIN CATCH
    SELECT @ErrorNo = ERROR_NUMBER()
        , @SPName = ERROR_PROCEDURE()
        , @ErrorMessage = ERROR_MESSAGE();

    ROLLBACK TRANSACTION t1
END CATCH

Upvotes: 1

Related Questions