SQL Server Stored procedure to increment date by 1 day for each row

rggaifut 于 2022-12-22 发布在其他

关注(0)|答案(1)|浏览(123)

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

sql-server

来源：https://stackoverflow.com/questions/74843824/stored-procedure-to-increment-date-by-1-day-for-each-row

1条答案

按热度按时间

dced5bon1#

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.

赞(0）回复(0）举报 2022-12-22

我来回答

SQL Server Stored procedure to increment date by 1 day for each row

1条答案

相关问题

热门标签

最新问答