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.
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.