Stored procedure to increment date by 1 day for each row-CodePudding

I need a stored procedure that goes through all the records and increases the date by 1 each time. I.e. every record in the table currently has the 13th June 2008. So afterward it should look like this:

14/Jun/2008 
15/Jun/2008 
16/Jun/2008

The code below cannot update the data row by row because the table ID column (SalesOrderID) is a big number.

ALTER PROCEDURE [dbo].[test_SalesOrderDateIncrement]
AS
BEGIN
    SET NOCOUNT ON 
    
    DECLARE @RowCount INT = 1,
            @TotalRows Int = 0,
            @Increment INT = 0

    SELECT @TotalRows = COUNT(0) 
    FROM SalesOrderHeader
    
    WHILE @RowCount <= @TotalRows
    BEGIN
        SET @Increment = @Increment   1

        UPDATE SalesOrderHeader 
        SET DueDate = DATEADD(DAY, @Increment, DueDate) 
        WHERE SalesOrderID = @RowCount

        SET @RowCount = @RowCount   1
    END
    
    SET NOCOUNT OFF
END

CodePudding user response：

Try something like this - properly set-based, does it in one go - no messy loop or anything.

CREATE OR ALTER PROCEDURE [dbo].[test_SalesOrderDateIncrement]
AS
BEGIN
    SET NOCOUNT ON;
    
    WITH DataToUpdate AS
    (
        SELECT
            SalesOrderID,
            RowNum = ROW_NUMBER() OVER (ORDER BY SalesOrderID)
        FROM
            Sales.SalesOrderHeader
    )
    UPDATE soh 
    SET soh.DueDate = DATEADD(DAY, dtu.RowNum, soh.DueDate)
    FROM Sales.SalesOrderHeader soh
    INNER JOIN DataToUpdate dtu ON dtu.SalesOrderID = soh.SalesOrderID;

    SET NOCOUNT OFF;
END

You didn't mention anything about any sort order, e.g. in which order the rows should have their DueDate incremented - so I just picked the SalesOrderID (the primary key) as the ordering criteria here - you might need to adjust this to your needs if they are different.