Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 */
share|improve this question

1 Answer 1

up vote 3 down vote accepted

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; 
share|improve this answer
    
Apparantly I didn't get the memo about using parenthesis. Thank you SQL2005. Awesome. –  RThomas Mar 8 '13 at 18:18
    
Also, I was trying to get around deprecation with the sub select but it's a nil issue now. Thanks again. –  RThomas Mar 8 '13 at 18:19
    
`@RThomas - Yes I deleted the bit about deprecation as I saw you had already mentioned this and was aware of it. –  Martin Smith Mar 8 '13 at 18:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.