TSQL : Using case to fill a string list with items-CodePudding

I am trying to create some logic using an case in an insert

this is the code i am using but it doesn't compile

INSERT INTO @MyList VALUES
(CASE @pCodTraitement
            WHEN   'CGPFDG'  then    '0220' 
            WHEN   'CGPPS'   then    '5275' 
            WHEN   'CGPFP'   then    '5276' 
            WHEN   'CGPGC'   then    '5277', '0220'                 
            ELSE '0241' 
END )

the solution '''5277''' ',' '''0220''' won't work

I need the 4th condition to be fulfilled like items not text because I want to use it after

I don't want it as a text block

because I will browse through its items

...

SELECT *
FROM MyTable
WHERE MyColumn IN (SELECT Value FROM @MyList)

...

CodePudding user response：

Since you need to insert multiple values under one of the conditions, and it isn't possible for a CASE to return multiple values in that way, you will need to break your operation apart and do something like

IF @pCodTraitement = 'CGPGC'
BEGIN
INSERT INTO @MyList
VALUES ('5277'), ('0220');
END ELSE
BEGIN
INSERT INTO @MyList VALUES
(CASE @pCodTraitement
            WHEN   'CGPFDG'  then    '0220' 
            WHEN   'CGPPS'   then    '5275' 
            WHEN   'CGPFP'   then    '5276' 
            ELSE '0241' 
END )
END

CodePudding user response：

Another way would be to use UNION ALL with multiple rows that match CGPGC:

INSERT INTO @MyList SELECT '0220' WHERE @pCodTraitement = 'CGPFDG'
          UNION ALL SELECT '5275' WHERE @pCodTraitement = 'CGPPS'
          UNION ALL SELECT '5276' WHERE @pCodTraitement = 'CGPFP'
          UNION ALL SELECT '5277' WHERE @pCodTraitement = 'CGPGC'
          UNION ALL SELECT '0220' WHERE @pCodTraitement = 'CGPGC';

Or to build a VALUES derived table and filter:

INSERT INTO @MyList
  SELECT outcol FROM 
  (
    VALUES('CGPFDG','0220'),
          ('CGPPS', '5275'),
          ('CGPFP', '5276'),
          ('CGPGC', '5277'),
          ('CGPGC', '0220')
  ) AS dv(pCodTraitement, outcol)
  WHERE pCodTraitement = @pCodTraitement;

CodePudding user response：

Guessing here, but maybe one of these solutions:

INSERT INTO @MyList (ColumName)
SELECT SS.[value]
FROM (VALUES(CASE @pCodTraitement WHEN 'CGPFDG' THEN '0220' 
                                  WHEN 'CGPPS' THEN '5275' 
                                  WHEN 'CGPFP' THEN '5276' 
                                  WHEN 'CGPGC' THEN '5277,0220'                 
                                  ELSE '0241' 
             END))V(S)
     CROSS APPLY STRING_SPLIT(V.S,',') SS;


DECLARE @Values table (CodTraitement, [value]);
INSERT INTO @Values (CodTraitement, [value])
VALUES('CGPFDG','0220'),
      ('CGPPS','5275'),
      ('CGPFP','5276'),
      ('CGPGC','5277'),
      ('CGPGC','0220');

INSERT INTO @MyList (ColumName)
SELECT ISNULL(V.[value],'0241')
FROM (VALUES(@pCodTraitement))p(CodTraitement)
     LEFT JOIN @Values V ON p.CodTraitement = V.CodTraitement;