主页 / dba / 问题 / 36270
Accepted
RThomas
Asked: 2013-03-09 10:03:49 +0800 CST

按时间顺序删除可变数量的记录

  • 772

我们的开发人员一直在存储过程中使用游标来删除旧密码历史记录。要删除的记录数由变量传递。

DECLARE hist_cursor CURSOR LOCAL FOR
    SELECT history_nr   FROM usr_pwd_hist   WHERE usr_id = @usr_id
    ORDER BY    history_nr ASC

OPEN hist_cursor

WHILE @to_delete > 0
BEGIN
    FETCH NEXT FROM hist_cursor INTO @hist_val

    DELETE FROM usr_pwd_hist WHERE CURRENT OF hist_cursor

    SET @to_delete = @to_delete-1
END

CLOSE hist_cursor; 
DEALLOCATE hist_cursor;

我想用基于集合的方法代替它。我不能做一个简单的 top 语句，因为要删除的记录数是一个变量。我不能在没有动态 sql 的情况下使用带有变量的 top，并且根据政策我们不允许在生产中使用动态 sql。

我正在考虑下面的这种方法，但它让我感到紧张，因为我知道 Microsoft 正计划改变 ROWCOUNT 影响返回结果的方式。通过将删除目标放在子查询中，我应该可以使用未来的 SQL 版本，但我仍然想知道是否有更好的方法来按时间顺序删除可变数量的记录。

SET ROWCOUNT @to_delete;  /* limit records to be deleted */
DELETE FROM usr_pwd_hist WHERE history_nr IN
(
    SELECT history_nr 
    FROM usr_pwd_hist
    WHERE usr_id = @usr_id
    ORDER BY history_nr ASC  
);
SET ROWCOUNT 0;  /* return rowcount to default setting */
sql-server-2012

1 个回答

  1. Best Answer

    您可以使用TOP变量。你只需要把它放在括号里。

    WITH T
     AS (SELECT TOP (@to_delete) *
         FROM   usr_pwd_hist
         WHERE  usr_id = @usr_id
         ORDER  BY history_nr ASC)
DELETE FROM T;
    • 3

相关问题