Home > DeveloperSection > Forums > Stored Procedure Error - Transaction Count mismatch
Simons Hood
Simons Hood

Total Post:70

Points:494
Posted on    January-21-2015 10:25 PM

 MSSQL Server SQL Server 
Ratings:


 1 Reply(s)
 601  View(s)
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



Brad Pitt
Brad Pitt

Total Post:61

Points:429
Posted on    January-21-2015 10:56 PM

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


Don't want to miss updates? Please click the below button!

Follow MindStick