T-SQL: Dynamic Value Incrementing

Posted on 17/07/2008. Filed under: Software Development |

Hi,

I have a business requirement to track when a user has reached a certain level.
Since this specification requires two fixed numbers 3 and 20 and the rest multiples of 25; I decided to write a little loop in SQL to perform this.
The code snippet is below and is very simple.

First I create a table to preserve the values I want.
I then insert the first record – number 3 into the table.
Now I will run a loop until a certain ceiling (500) is reached.
The loop will create increments of 25 from 0 until 500 is reached.
With every loop the new number will be inserted into the table.

Code Sample:

CREATE TABLE #t (milestones int PRIMARY KEY)

–insert the first milestone = 3
INSERT INTO #t (milestones)
VALUES (3)
GO

–insert the sequence increments of 25 milestones

DECLARE @count int, @year int
SET @year = 0
SET @count = 0

WHILE @count < 25*20
BEGIN

SET @count = @count + 25;

INSERT INTO #t (milestones)
VALUES (@count);

END
GO

SELECT * FROM #t
GO

At the end the table select will contain the following values.

3
25
50
75
100
125
150
175
200
225
250
275
300
325
350
375
400
425
450
475
500

Note: The Primary Key setting on the table is not required but has a significant performance improvement.

For demonstration purposes 20 is left out due to other requirements that must be validated first and is not relevant to this sample.

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: