troubleshooting Question

Delete item from table where date is older than x number of units (day, month, year)

Avatar of dij8
dij8Flag for New Zealand asked on
Microsoft SQL Server
5 Comments1 Solution1094 ViewsLast Modified:
I have a delete stored procedure that works perfectly.  Except now it needs to not run unless a DeleteDate value is more than a specified number of days, weeks, or years ago.  I have a table that stores the number of units as an int and the unit as a char(5).  The DATEADD function does not seem to like a variable for the first value so I am trying to use a CASE staement in the WHERE clause.  I don't even know if this is allowed.

The stored procedure I have is below.  As it stands it fails a syntax check on the ">" symbol on line 19 (the "WHEN 'Day'... line) and the "," symbol on line 20 and 21.  If the logic makes sense and someone can help me with a working version excellent.  If it doesn't make sense, just ask and I will try to clarify.

DELETE PageContainerModule
      FROM PageContainerModule INNER JOIN PageContainer
            ON PageContainer.PageContainerId = PageContainerModule.PageContainerId
            INNER JOIN Page
            ON PageContainer.PageId = Page.PageId
      WHERE Page.OnHoldTypeId = (SELECT LookupValueId FROM LookupValue WHERE [Name] = 'Delete')
            AND CASE (SELECT TOP 1 PurgeCycleUnit FROM Site WHERE SiteId=Page.SiteId)
                  WHEN 'Day' THEN DATEADD(Day, (SELECT PurgeCycleFrequency FROM Site WHERE SiteId=Page.SiteId), Page.DeleteDate) > GETDATE()
                  WHEN 'Month'  THEN DATEADD(Month, (SELECT PurgeCycleFrequency FROM Site WHERE SiteId=Page.SiteId), Page.DeleteDate) > GETDATE()
                  WHEN 'Year'  THEN DATEADD(Year, (SELECT PurgeCycleFrequency FROM Site WHERE SiteId=Page.SiteId), Page.DeleteDate) > GETDATE()

 Without the "AND CASE" and onwards part of the Stored Procedure it works without fail.
ASKER CERTIFIED SOLUTION
dqmq

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros