DGibbs DGibbs - 5 months ago 11
SQL Question

SQL get fields which are null/empty for each record returned

I have the below query which I'm using to retrieve records which have at least one of the following columns set as

NULL
or
''
:


  • MainImage

  • Summary

  • Description

    DECLARE @missingFields varchar(100)
    SET @missingFields = ''

    SELECT

    c.[UnitReference] as 'UnitRef',
    t.[NodeName] as 'Property',
    @missingFields as 'Field/s missing'

    FROM [DetailPage] d
    INNER JOIN [CRM] c
    ON d.ItemID = c.ItemID
    INNER JOIN
    [Tree] t
    ON d.[ID] = t.[ID]

    WHERE (ISNULL(d.MainImage, '') = '' OR ISNULL(d.Summary,'') = '' OR ISNULL(d.[Description],'') = '')
    AND c.[IsListed] = 1 AND c.[IsMarketed] = 1



This returns the data I want however I also need to build up a string which lists which of the columns is null or empty for the returned record, e.g.
"Main Image is empty, Description is empty"
when a record has empty
MainImage
and
Description
columns.

I've tried:

@missingFields = CASE WHEN ISNULL(MainImage, '') = '' THEN 'Main image is null' ELSE '' END -- etc...


But I can't include this with the data retrieval operations. How would I go about doing this?

Answer
    SELECT c.[UnitReference] AS 'UnitRef',
           t.[NodeName] AS 'Property',
           ( CASE
                 WHEN d.MainImage IS NULL
                 THEN 'Main Image is Null, '
                 WHEN d.MainImage = ''
                 THEN 'Main Image is Empty, '
                 ELSE ''
             END )+
             ( CASE
                WHEN d.Summary IS NULL
                THEN 'Summary is Null, '
                WHEN d.Summary = ''
                THEN 'Summary is Empty, '
                ELSE ''
            END )+
            ( CASE
                WHEN d.[Description] IS NULL
                THEN 'Description is Null, '
                WHEN d.[Description] = ''
                THEN 'Description is Empty, '
                ELSE ''
            END ) AS MissingFields
    FROM [DetailPage] d
    INNER JOIN [CRM] c
    ON d.ItemID = c.ItemID
    INNER JOIN [Tree] t
    ON d.[ID] = t.[ID]
    WHERE( d.MainImage IS NULL OR d.MainImage = '' )
     OR ( d.Summary IS NULL OR d.Summary = '' )
     OR ( d.[Description] IS NULL OR d.[Description] = '' )
     AND c.[IsListed] = 1
     AND c.[IsMarketed] = 1