Nullable smallint column usage in a CASE Statement nested in a STUFF function

Question

I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (can you even convert a smallint to an string?) and I'm still not getting results as expected. I'd like the WHEN and the ELSE to combine when they meet the specific conditions in my query, but instead it is completely eliminating this row from my results.

In the STUFF, if the row is Restricted, I’d like to tack on “(Restricted)” to the ItemStatus. If not Restricted (0 or NULL), I'd like to just tack on the ItemStatus.

I tried replacing the WHEN in the CASE statement with something like:

WHEN CONVERT(varchar(100),  ISNULL(Statuses2.Restricted, 0) ) = 0 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'

If I change the WHEN to:

WHEN Statuses2.Restricted IS NOT NULL THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'

...I get the row to show up (but only with the ItemStatus. This column should have 2 strings concatenated as there is at least one Restricted and non-Restricted value)…but once I add an additional condition to try and check the value of Restricted (Restricted = 0 or Restricted = 1), I start not getting any results at all.

Any ideas of what I'm doing wrong?

SELECT
    StatusList
    --etc...
FROM
(
    SELECT
        StatusList
        --etc...
    FROM
    (
        SELECT
            StatusList,
            [Count] = ISNULL(COUNT(*), 0)
            --etc...
        FROM
            Items
        INNER JOIN
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY /* some stuff */ ) as RowNum, Statuses.*,
                STUFF((
                    SELECT
                        CASE
                            WHEN Statuses2.Restricted = 1 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'
                            ELSE ', ' + dmStatuses2.ItemStatus
                        END
                    FROM Statuses Statuses2 
                    WHERE
                        Statuses2.StatusId = Statuses.StatusId
                     FOR XML PATH('')
                ), 1, 2, '')
                     AS StatusList
            FROM Statuses
            INNER JOIN dmStatuses ON
                dmStatuses.StatusId = Statuses.StatusId
            WHERE
                Statuses.TableId = @TableId
        ) AS Statuses2 ON
            Statuses2.TableId = Items.TableId
            AND Statuses2.EndDt IS NULL
            AND RowNum = 1
        INNER JOIN dmStatuses dmStatuses2 ON
            dmStatuses2.StatusId = Statuses2.StatusId
        LEFT JOIN dmItems dmItems2 ON
            dmItems2.ItemId = Items.ItemId
        WHERE
            Items.TableId = @TableId
        GROUP BY
            Items.TableId,
            dmItems2.ItemId,
            Statuses2.StatusList
    ) AS ItemCounts
    LEFT JOIN dmItems ON
        dmItems.ItemId = ItemCounts.ItemId
) AS ItemCountsAndLimits
WHERE
    ItemCountsAndLimits.[Count] > ItemCountsAndLimits.Limit

Answer 1

Another way to create your StatusList is, using STRING_AGG (From SQL Server 2017):

SELECT STRING_AGG(x.status,',') FROM (
   SELECT
      ROW_NUMBER() OVER (PARTITION BY StatusId) as RowNum,
      CASE WHEN Status.Rescriced = 1 THEN ItemStatus + ' (Restricted)'
           ELSE ItemStatus END AS Status
   FROM Statuses) x

Above query is untested, and should be adapted to your situation.

Answer 2

I believe if you change your CASE statement around to evaluate for a NULL, you should be fine, so something like the following:

CASE
    WHEN Statuses2.Restricted IS NULL OR Statuses2.Restricted = 0 THEN ', ' + dmStatuses2.ItemStatus
    ELSE ', ' + dmStatuses2.ItemStatus + ' (Restricted)'
END

Notice that I evaluate for NULL or 0 rather than 1, basically reversing your logic as that should properly trap the NULL per your description.

Also, it looks like you're performing an inner join between dmStatuses2 and Statuses2 which by design filters out all the NULL fields, so you'll want to change this to a LEFT JOIN:

LEFT JOIN dmStatuses dmStatuses2 ON
        dmStatuses2.StatusId = Statuses2.StatusId

Answer 3

In my WHERE clause for the STUFF, I needed to add a check for the ItemId instead of the TableId. I also had to add additional joins to the Items, dmItems, and dmStatuses tables inside of the STUFF and use those tables when concatenating the STUFF instead of outer tables that I was using previously.