The problem I see with your procedure is that the @mbatch variable is not set to the max.

If you alter your procedure to the following it should work.

CREATE PROCEDURE [prMTRANS_INSERT]

(@MTRANS_ID int OUTPUT,

@MStatus char(1),

@MBatch int OUTPUT

)

As

BEGIN

Begin

-- Changed your query here

SELECT TOP 1 @mbatch = [MBatch] FROM [MTRANS] ORDER BY [MTRANS_ID] DESC

--SET @mbatch = [MBatch] -- Remove this

if @mbatch >= 999

begin

set @mbatch = 1

end

else

set @mbatch = @mbatch +1

INSERT INTO [MTRANS]([MStatus], [MBatch])

VALUES (@mstatus,@mbatch)

End

SET @MTRANS_ID = SCOPE_IDENTITY()

-- SELECT @@IDENTITY as TRANS_ID, @mbatch as BATCHNO -- Not sure if you want to do this

END

Tested with

declare @oMID INT, @oBatch INT

exec prMTRANS_INSERT @MTRANS_ID = @oMID , @MStatus = 'B', @MBatch = @oBatch

GO 1500

SELECT * FROM [MTRANS]

Having said that, are all of your inserts one at a time and are they all through this procedure? You may run into issues if they aren't.

In 2012 you could use a sequence with cycling as the default value on the batch.

Edit: Fixed a syntax error and return values