Tonight I was writing the stored procedure which will return lists of data to
the Codewise nighly updates. Basically, each night, Codewise will call a
webservice and you need to return a list of all GUID's which have been either
ADDED, UPDATED or REMOVED since the last time they called your service.
The return Xml will look something like this:
<ContentItemIds>
<Added>
<Guid ... />
</Added>
<Updated>
<Guid ... />
</Updated>
<Removed>
<Guid ... />
</Removed>
</ContentItemIds>
I wanted to have a stored procedure that I could pass a "type" and a
start date and get back a list of Guids that have changed to that type since the
starting date. This kind of Sql is often munged-up as a string and Exec'd
back to the caller. I'm not a big fan of sql strings in the middle-tier or
building strings in the database so I thought that I'd find a straight sql
approach. I was trying the following query which was failing:
SELECT CASE
WHEN @diffType = 1 AND [CreationDate] > @beginDate THEN [GUID]
WHEN @diffType = 2 AND [LastUpdatedDate] > @beginDate THEN [GUID]
WHEN @diffType = 3 AND [RemovedDate] > @beginDate THEN [GUID]
END As [GUID]
FROM dbo.Codewise_ContentItem
WHERE 1 IS NOT NULL
So, I pinged Thomas who
whipped up the following pre-breakfast solution:
SELECT [GUID]
FROM dbo.Codewise_ContentItem
WHERE [GUID] IN (
SELECT
CASE
WHEN @diffType = 1 AND [CreationDate] > @beginDate THEN [GUID]
WHEN @diffType = 2 AND [LastUpdatedDate] > @beginDate THEN [GUID]
WHEN @diffType = 3 AND [RemovedDate] > @beginDate THEN [GUID]
END As [GUID]
FROM dbo.Codewise_ContentItem
)