Nested sub-queries always elude me
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 )