Reset a Variable that stores the number of times a Query runs back to 1 when the Count reaches a certain number?

Question

I have a variable that stores the number of times a query runs, but it has a length limitation of 3 characters, which I can't modify. As such, once the Count reaches 999, I assume the logic will fail.

SET @QueryCount = (
    SELECT COUNT(1) FROM dbo.Records WHERE QueryName = @QueryName
);

Currently I have no logic to account for a Count that is over 3 characters, and the expectation is that the Variable will be reset to a Count of 1 once it reaches 999.

Answer 1

You can try and do integer division:

SET @QueryCount = (
    SELECT COUNT(1) - COUNT(1) / 1000 * 1000
    FROM dbo.Records 
    WHERE QueryName = @QueryName
);

This garantees that the value will belong to range 0..999. When the value reaches 1000, it resets to 0.

Demo of the arithmetic logic:

DECLARE @value int = 999
SELECT @value - @value / 1000 * 1000

Yieds: 999

DECLARE @value int = 1000
SELECT @value - @value / 1000 * 1000

Yields: 0

DECLARE @value int = 3525
SELECT @value - @value / 1000 * 1000

Yields: 525