[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

Create comma seperated list if not null

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.
0
UniqueData
Asked:
UniqueData
  • 2
1 Solution
 
LowfatspreadCommented:
can you give some examples of what you want to achieve...
0
 
UniqueDataAuthor Commented:
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
etc...

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.
0
 
ZberteocCommented:
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 ',':

replace(
replace(
      CASE
            WHEN EstimatedCloseDate IS NULL THEN 'No Close Date,'
            ELSE ''
      END +
      CASE
            WHEN TotalContractAmount IS NULL THEN 'No Contract Amt,'
            ELSE ''
      END +
      CASE
            WHEN NeedsUnits=1 AND (NumberOfUnits IS NULL OR UnitCost IS NULL) THEN 'No Close Date,'
            ELSE ''
      END+'##@@&&'
      ,',##@@&&',''),'##@@&&','')
0
 
UniqueDataAuthor Commented:
That worked perfectly.  Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now