STORED PROCEDURE ERROR - TRANSACTION COUNT MISMATCH

Simons Hood

Total Post:70

Points:494
Posted by  Simons Hood
 924  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. Brad Pitt

    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

Answer

NEWSLETTER

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