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