Created
December 10, 2012 04:29
-
-
Save NickJosevski/4248381 to your computer and use it in GitHub Desktop.
purge loop, why?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SET @rows = 5 --default | |
IF @purgeDate is NULL SET @purgeDate=getdate() | |
-- Only execute if there is work to do and continue | |
-- until all records with a PurgeDate <= now are deleted | |
WHILE EXISTS(SELECT * FROM WorkItemStatus WHERE PurgeDate <= @purgeDate) | |
BEGIN | |
-- NB: the FK in WorkStatus has ON DELETE CASCADE, | |
-- so it will delete corresponding rows automatically | |
DELETE TOP (@rows) | |
FROM WorkItemStatus | |
WHERE PurgeDate <= @purgeDate | |
END -- WHILE EXISTS( |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment