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

• HandyD: It might help to include table DDL, sample data and expected results in your question.
• eckes: The conversion to varchar looks unneeded (in fact wrong if you compare numerically). The isnull is just fine.

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.

• thecoolmacdude: Thanks for the suggestion - I checked the version of SQL Server we are using and it is SQL Server 2014, so this won't work for me.

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

• thecoolmacdude: When I try this, I don't get any rows at all. If I take out the second part of the OR (Statuses2.Restricted = 0), I'm not getting a concatenated list -- it's only showing me the ELSE "Restricted" value. Something strange is going on with the Restricted column. I should be getting two concatenated values - one from the WHEN and a second from the ELSE.
• John Eisbrener: @thecoolmacdude I updated the answer based on your feedback as I didn't look close enough at your join logic. I think changing the join in combination with not evaluating for NULLs will solve your issue.

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.