create PROCEDURE [dbo].[GetNextCaseNumber]
  @fieldName varchar(50),
  @incrementBy int,
  @newValue varchar(500) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @currentValue varchar(500);
  DECLARE @year int = Right(YEAR(GETDATE()), 2);

  BEGIN TRANSACTION
  
	  -- Handle null values and initialize to 1 if no record found
	  SELECT @currentValue = FieldValue FROM SLCSetting With (TABLOCKX) WHERE FieldName = @fieldName

	  IF @currentValue IS NULL
	  BEGIN
		SET @currentValue = CAST(@year as varchar(2)) + '000000'
		INSERT INTO SLCSetting (FieldName, FieldValue) VALUES (@fieldName, @currentValue)
	  END

	  DECLARE @currentYear int = CAST(LEFT(@currentValue, 2) as int);
	  DECLARE @currentNumber int = CAST(RIGHT(@currentValue, 6) AS int);

	  if @year > @currentYear
	  BEGIN
		set @currentYear = @year
		set @currentNumber = 0
	  END

	  -- Increment the value and update the table
	  set @currentNumber = @currentNumber + @incrementBy

	  -- Check if current value is at the maximum for the year
	  if @currentNumber > 999999
	  begin
		set @currentNumber = @incrementBy
		set @currentYear = @currentYear + 10
	  end

	  SET @currentValue = CAST(@currentYear as varchar(2)) + RIGHT('000000' + CAST(@currentNumber AS varchar(6)), 6)
	  
	  UPDATE SLCSetting SET FieldValue = @currentValue WHERE FieldName = @fieldName

  COMMIT TRANSACTION
  -- Return the new value through the OUTPUT parameter
  SET @newValue = @currentValue;
END