Sql-server – Nullable smallint column usage in a CASE Statement nested in a STUFF function

casequerysql serverstored-procedures

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

Best Answer

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.

Related Question