Delete a variable number of records by a chronological order

Question

Our developers have been using a cursor within a stored procedure to delete old password history. The number of records to delete is being passed by variable.

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;

I would like to replace this with a set based approach. I can't do a simple top statement because the number of records to delete is a variable. I can't use a top with a variable without dynamic sql and by policy we don't allow dynamic sql in production.

I'm considering this approach below but it makes me nervous as I know that Microsoft is planning on changing the way ROWCOUNT affects return results. By putting the delete targets in a subquery I should be ok with future SQL versions, but I'm still wondering if there is a better way to delete a variable number of records by a chronological order.

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 */

Answer 1

You can use TOP with a variable. You just need to put it in parentheses.

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