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