CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2012
SBB
SBB

Reputation: 8970

TSQL Incrementing Count of Variable

I have a UI that allows a user to select one or more fields they want to add to a table. This data also has an orderID associated with it that determines the field order.

When the user adds new fields, I need to find the last orderID this user used and increment it by 1, submitting all of the new fields.

For example, if there is a single record that already exists in the database, it would have an orderID of 1. When I choose to add three more fields, it would check to see the last orderID I used (1) and then increment it for each of the new records it adds, 1-4.

-- Get the last ID orderID for this user and increment it by 1 as our starting point
DECLARE @lastID INT = (SELECT TOP 1 orderID FROM dbo.BS_ContentRequests_Tasks_User_Fields WHERE QID = @QID ORDER BY orderID DESC)
SET @lastID = @lastID+1;

-- Create a temp table to hold our fields that we are adding
DECLARE @temp AS TABLE (fieldID int, orderID int)

-- Insert our fields and incremented numbers
INSERT INTO @temp( fieldID, orderID )
SELECT ParamValues.x1.value('selected[1]', 'int'),
       @lastID++
FROM   @xml.nodes('/root/data/fields/field') AS ParamValues(x1);

Obviously the @lastID++ part is where my issue is but hopefully it helps to understand what I am trying to do.

What other method could be used to handle this?

Upvotes: 0

Views: 1011

Answers (3)

square_particle
square_particle

Reputation: 524

If you want OrderID to be unique across the entire table then see below:

Click here to take a look at another post that addresses this issue.

There are multiple ways to approach this issue, but in this case, the easiest, while reasonable, means may be to use an identity column. However, that is not as extensible as using a sequence. If you feel that you may need more flexibility in the future, then use a sequence.

If you want OrderID to be unique across the fields inserted in one batch then see below:

You should take a closer look at Chris Steele's answer.

Upvotes: 0

Logar314159
Logar314159

Reputation: 503

You could use a column with IDENTITY(1,1)

Upvotes: 0

Chris Steele
Chris Steele

Reputation: 1381

ROW_NUMBER() ought to do it.

select  x.Value,
        ROW_NUMBER() over (order by x.Value) + @lastID
    from (
        select 10 ParamValues.x1.value('selected[1]', 'int') Value
            from @xml.nodes('/root/data/fields/field') AS ParamValues(x1)
        ) x

Upvotes: 1

Related Questions