Stored Procedure Error - Transaction Count mismatch

Total Post:70

Points:494
 1225  View(s)
Ratings:
Rate this:

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

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

  1. Post:61

    Points:429
    Re: Stored Procedure Error - Transaction Count mismatch

    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

      Modified On Apr-08-2018 11:44:23 PM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!