Attention: We have retired the ASP.NET Community Blogs. Learn more >

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
)

 

4 Comments

  • Isn't this one simpler?



    SELECT [GUID]

    FROM dbo.Codewise_ContentItem

    WHERE CASE

    WHEN @diffType = 1 AND [CreationDate] &gt; @beginDate THEN 1

    WHEN @diffType = 2 AND [LastUpdatedDate] &gt; @beginDate THEN 1

    WHEN @diffType = 3 AND [RemovedDate] &gt; @beginDate THEN 1

    ELSE 0

    END = 1

  • Perfect! That's what I was after :-)

  • Even this



    SELECT [GUID]

    FROM dbo.Codewise_ContentItem

    WHERE

    (@diffType = 1 AND [CreationDate] &gt; @beginDate) OR

    (@diffType = 2 AND [LastUpdatedDate] &gt; @beginDate) OR

    (@diffType = 3 AND [RemovedDate] &gt; @beginDate)



    In this case subquery should not be used since it can be executed without any join. It should be a hell faster.

  • view for less for more detail

Comments have been disabled for this content.