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?

            [Count] = ISNULL(COUNT(*), 0)
        INNER JOIN
            SELECT ROW_NUMBER() OVER (PARTITION BY /* some stuff */ ) as RowNum, Statuses.*,
                            WHEN Statuses2.Restricted = 1 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'
                            ELSE ', ' + dmStatuses2.ItemStatus
                    FROM Statuses Statuses2 
                        Statuses2.StatusId = Statuses.StatusId
                     FOR XML PATH('')
                ), 1, 2, '')
                     AS StatusList
            FROM Statuses
            INNER JOIN dmStatuses ON
                dmStatuses.StatusId = Statuses.StatusId
                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
            Items.TableId = @TableId
        GROUP BY
    ) AS ItemCounts
    LEFT JOIN dmItems ON
        dmItems.ItemId = ItemCounts.ItemId
) AS ItemCountsAndLimits
    ItemCountsAndLimits.[Count] > ItemCountsAndLimits.Limit
  • It might help to include table DDL, sample data and expected results in your question. – HandyD Nov 15 '19 at 2:59
  • The conversion to varchar looks unneeded (in fact wrong if you compare numerically). The isnull is just fine. – eckes Nov 20 '19 at 10:18

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

SELECT STRING_AGG(x.status,',') FROM (
      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.

  • 1
    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. – thecoolmacdude Nov 18 '19 at 14:21

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

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

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
  • 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. – thecoolmacdude Nov 18 '19 at 14:20
  • @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. – John Eisbrener Nov 18 '19 at 15:42

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.