USE [ShipLinkCentralDB]
GO
/****** Object:  StoredProcedure [dbo].[GetNextCaseNumber]    Script Date: 03/14/2023 6:22:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[GetNextNumber]
  @fieldName varchar(50),
  @incrementBy int,
  @digitLimit int,
  @newValue varchar(500) OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @currentValue varchar(500);

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

		IF @currentValue IS NULL
		BEGIN
		SET @currentValue = '0'
		INSERT INTO SLCSetting (FieldName, FieldValue) VALUES (@fieldName, @currentValue)
		END

		DECLARE @currentNumber int = CAST(@currentValue AS int);

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

		-- Check if current value is at the maximum
		if @currentNumber > CAST(REPLICATE('9', @digitLimit) AS int)
		begin
		set @currentNumber = @incrementBy
		end

		SET @currentValue = CAST(@currentNumber AS varchar(9))
	  
		UPDATE SLCSetting SET FieldValue = @currentValue WHERE FieldName = @fieldName

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