Create comma seperated list if not null

Posted on 2007-10-09
Last Modified: 2008-01-09
In Access I can use:
     strParam1 & (', ' + strParam2) & (', ' + strParam3)

Using the + will return null if the param part is null (no comma will appear)

I am trying to do the same in a Stored Procedure.  The closest I got was:
            (isnull((', '+ Case When EstimatedCloseDate is null then
                  'No Close Date' end),'') + isnull( (', ' +
            (Case When TotalContractAmount is null then
                  'No Contract Amt' end)),'') + isnull(( ', ' +
            (Case When (NeedsUnits=1 and (NumberOfUnits is null or UnitCost is null)) then
                  'Missing Units' end)),''))as MissingItems

and I was going to try and find the code that will strip out the first two characters, but there has got to be a cleaner way...

Thanks in advance.
Question by:UniqueData
    LVL 50

    Expert Comment

    can you give some examples of what you want to achieve...
    LVL 7

    Author Comment

    I am trying to make  a comma seperated list of 3 items that may be missing and there are several possible combinations, such as:

    No Close Date, Missing Units
    No Contract Amount, Missing Units
    Missing Units
    No Contract Amount, No Close Date, Missing Units

    But I don't want the comma to always show up because of the cases where there is only one item to list plus I don't want an extra comma at the end.
    LVL 26

    Accepted Solution

    I used a trick to get rid of the last come by adding some text at the end. The scecond replace is for the case when all af the cases result in '' (empty) so there is no ',':

                WHEN EstimatedCloseDate IS NULL THEN 'No Close Date,'
                ELSE ''
          END +
                WHEN TotalContractAmount IS NULL THEN 'No Contract Amt,'
                ELSE ''
          END +
                WHEN NeedsUnits=1 AND (NumberOfUnits IS NULL OR UnitCost IS NULL) THEN 'No Close Date,'
                ELSE ''
    LVL 7

    Author Comment

    That worked perfectly.  Thanks

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now