forum

Home / DeveloperSection / Forums / Stored Procedure Error - Transaction Count mismatch

Stored Procedure Error - Transaction Count mismatch

Anonymous User 1927 21-Jan-2015

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


Updated on 21-Jan-2015
I am a content writter !

Can you answer this question?


Answer

1 Answers

Liked By