Skip to content

Instantly share code, notes, and snippets.

@NickJosevski
Created December 10, 2012 04:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save NickJosevski/4248381 to your computer and use it in GitHub Desktop.
Save NickJosevski/4248381 to your computer and use it in GitHub Desktop.
purge loop, why?
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