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:

Field Type
OrderID Uniqueidentifier FK to the the order table.
LineNumber Int The column that we need each entry to unique per OrderId, but always start from 0.
Item Varchar(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
This entry was posted in Brian's Code Extreme, SQL, T-SQL and tagged , , . Bookmark the permalink.

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.