Incrementing ID on a SQL row that you cannot use the Identity property with.

This is most common when the identity is not the primary key, and is tracked independently for each Foreign Key Ref.

Example:

FieldType
OrderIDUniqueidentifierFK to the the order table.
LineNumberIntThe column that we need each entry to unique per OrderId, but always start from 0.
ItemVarchar(MAX)

A simple solution, (simplifying the table above to single column) would be:

Lets set up our test environment.

DECLARE @X int = null
DECLARE @T Table(row INT)

Run this once as is, then run this once with the next two lines commented out.

INSERT INTO @T (row) 
VALUES (0),(1),(2),(3),(4),(5),(6)

Here is the bulk of the actual work that you should learn from.

SET @X = 0 
IF (
(SELECT COUNT(1) FROM @T) > 0 )
BEGIN
SELECT @X = MAX(row) + 1 FROM @T
END

And your proof

SELECT @X

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.