对结果集中的每一行执行动态更新语句

Question

我正在尝试编写一个数据滚动脚本，该脚本将定位演示数据库中的所有日期时间列，并将它们向前滚动 x 天。由于我们的数据库架构尚未设置（仍在开发中），我试图尽可能保持动态。除了几列（我稍后将排除）之外，此语句标识了一个更新语句，它将向前滚动日期时间列：

SELECT 

    'UPDATE ' + [isc].[TABLE_NAME] + ' SET ' +
    [isc].[COLUMN_NAME] + 
    '= ' + [isc].[COLUMN_NAME] + '+ ' + 
    CAST(@DaysToRollForward AS NVARCHAR(5))  AS DySQL

FROM [INFORMATION_SCHEMA].COLUMNS AS isc
INNER JOIN [INFORMATION_SCHEMA].tables AS ist
    ON [ist].[TABLE_NAME] = [isc].[TABLE_NAME]
        WHERE [isc].[DATA_TYPE] = 'datetime'
            AND [ist].[TABLE_TYPE] = 'base table' 

我不反对使用游标，因为这只是一个演示服务器，并且永远不会真正看到太多的负载。我们这样做只是为了保持记录最新，以便我们始终在应用程序中看到数据。我已经尝试过下面的游标，它没有执行 UPDATE 语句。有什么想法吗？我的想法正确吗？我在这里看到了一些帮助帖子，但大多数都是在 BEGIN - END 块中执行存储过程。另外，如果有一个基于集合的方法，我会对此感兴趣，尽管如果有一个简单的修复我的光标的方法也很好。正如我提到的，这只是一个演示/质量保证服务器。

USE [sCRMDB1_demo]

GO

DECLARE @OrganizationId BIGINT
DECLARE @dySQL NVARCHAR(256)
DECLARE @DaysToRollForward INT


SET @DaysToRollForward = 7

DECLARE db_cursor_rollbackdates CURSOR FOR

SELECT 
    'UPDATE ' + [isc].[TABLE_NAME] + ' SET ' +
    [isc].[COLUMN_NAME] + 
    '= ' + [isc].[COLUMN_NAME] + '+ ' + 
    CAST(@DaysToRollForward AS NVARCHAR(5))  AS DySQL

FROM [INFORMATION_SCHEMA].COLUMNS AS isc
INNER JOIN [INFORMATION_SCHEMA].tables AS ist
    ON [ist].[TABLE_NAME] = [isc].[TABLE_NAME]
        WHERE [isc].[DATA_TYPE] = 'datetime'
            AND [ist].[TABLE_TYPE] = 'base table' 


OPEN db_cursor_rollbackdates
FETCH NEXT FROM db_cursor_rollbackdates INTO @dySQL

WHILE @@FETCH_STATUS = 0

BEGIN
    --PRINT CAST(@dySQL AS NVARCHAR(200))
    EXEC (@dySQL)
    FETCH NEXT FROM db_cursor_rollbackdates
END

CLOSE db_cursor_rollbackdates
DEALLOCATE db_cursor_rollbackdates

Answer 1

你会踢自己的...

在循环中，

FETCH NEXT FROM db_cursor_rollbackdates

这本质上只是一个 select 语句，并且 @dySQL 保持不变，因此你的行 EXEC(@dySQL) 一遍又一遍地执行相同的更新，这就是为什么你不这样做查看数据库中更新的日期。您需要将其更改为：

FETCH NEXT FROM db_cursor_rollbackdates INTO @dySQL

这无法在基于集合的解决方案中完成，因为您正在更新多个表中的多个列。在替代解决方案方面，搜索“游标与临时表”并自行决定性能影响。我个人更喜欢临时表方法，但是如果我建议这是“正确”的方法，可能会被一些人处以私刑！另外，如果您有很多包含多个日期时间列和大量数据的表，那么您可以通过使用临时表和使用嵌套循环来稍微提高性能，即循环遍历至少包含一个日期时间列的所有表，然后在每个循环中循环通过所有日期时间列为每个表动态构建 SQL，以便每个表只执行一次更新，无论它们有多少个日期时间列。由于这是一个开发服务器，并且在生产服务器上不需要相同的过程，如果我是您，我就不会担心它的性能，只要它可以专注于其他开发领域即可！

最后请注意其他日期格式（Smalldatetime、Date、Datetime2），您的光标目前不会选择它们。