CODEWITHSUNDEEP
sqlsql-servert-sql
JonWay
JonWay

Reputation: 1735

CASE expression for multiple parameters

I have this data:

CREATE TABLE #MyTable (Names VARCHAR(50), Category VARCHAR(50), OrderDate DATE, Quantity INT)
INSERT INTO #MyTable VALUES
       ('Buchanan', 'Dairy', '2019-10-08', 12),
       ('Buchanan', 'Grains', '2019-11-18', 3),
       ('Buchanan', 'Dairy', '2019-07-15', 3),
       ('Suyama', 'Produce', '2019-07-08', 18),
       ('Suyama', 'Produce', '2019-09-03', 27),
       ('Peacock', 'Seafood', '2019-10-30', 37),
       ('Peacock', 'Produce', '2019-07-24', 15),
       ('Peacock', 'Condiments', '2019-07-29', 23),
       ('Leverling', 'Grains', '2019-06-05', 49),
       ('Leverling', 'Cereals', '2019-08-07', 31),
       ('Leverling', 'Condiments', '2019-08-21', 42),
       ('Peacock', 'Confections', '2019-12-16', 17),
       ('Peacock', 'Dairy', '2019-09-04', 15),
       ('Peacock', 'Dairy', '2019-11-17', 44),
       ('Leverling', 'Dairy', '2019-11-12', 11),
       ('Leverling', 'Beverages', '2019-06-19', 50),
       ('Leverling', 'Confections', '2019-06-22', 7),
       ('Buchanan', 'Beverages', '2019-12-18', 23),
       ('Buchanan', 'Poultry', '2019-11-09', 29),
       ('Buchanan', 'Produce', '2019-08-18', 14);

What I want is to be able to supply multiple parameters to the months.

Using this query

DECLARE @Months VARCHAR(10)
SET @Months = 'Jun-19','Aug-19'

SELECT
    Names,
    Category,
    OrderDate,
    Quantity,
    FORMAT(OrderDate,'MMM-yy') AS MonthYear,
    CASE
       WHEN FORMAT(OrderDate,'MMM-yy') IN (@Months) THEN Quantity
    END AS MonthSelected
FROM #MyTable
ORDER BY OrderDate

If I supply a single value for example 'Jun-19' , it works fine.

However if I supplied more than one value eg 'Jun-19','Aug-19' it throws an error:

Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ','

Any help on how I can pass in multiple months to the parameter?

Desired output when passing 'Jun-19' ,'Aug-19' as example

NOTE I cannot use the parameter in where clause as the client wants to see all data and month not selected should be NULL

Desired output

enter image description here

Upvotes: 3

Views: 1645

Answers (6)

Marc Guillot
Marc Guillot

Reputation: 6465

You can't supply multiple values into a varchar variable this way :

DECLARE @Months VARCHAR(10)

SET @Months = 'Jun-19','Aug-19'

But you can use a separator, and read those different values with the string_split function :

DECLARE @Months VARCHAR(512)

SET @Months = 'Jun-19,Aug-19'

The query will now be :

SELECT 
    Names,  
    Category,   
    OrderDate,
    Quantity,
    FORMAT(OrderDate,'MMM-yy') AS MonthYear,
    CASE 
       WHEN FORMAT(OrderDate,'MMM-yy')  IN  (SELECT value FROM STRING_SPLIT(@Months, ','))
          THEN Quantity 
    END AS MonthSelected    
FROM
    #MyTable
ORDER BY 
    OrderDate

Upvotes: 2

Prajakta Kale
Prajakta Kale

Reputation: 399

You need to update @Month variable size as

DECLARE @Months VARCHAR(50)

We cannot declare variable like @Months = 'Jun-19','Aug-19' it should be @Months = 'Jun-19,Aug-19'.

You need first split the string by ',' and store result in a temporary table.

SELECT value  INTO #temptable FROM STRING_SPLIT(@Months, ',')

and then insert data from that temp table. 

   WHEN FORMAT(OrderDate,'MMM-yy')  IN (SELECT value  from #temptable)

and then delete temporary table as 

DROP TABLE #temptable;

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272446

Here is one clumsy way:

DECLARE @Months VARCHAR(100) = 'Jun-19,Aug-19';
SELECT Names
     , Category
     , OrderDate
     , Quantity
     , FORMAT(OrderDate,'MMM-yy') AS MonthYear
     , CASE WHEN ',' + @Months + ',' LIKE '%,' + FORMAT(OrderDate,'MMM-yy') + ',%' THEN Quantity END AS MonthSelected
FROM #MyTable
ORDER BY OrderDate

And an alternate that uses table variables:

DECLARE @Months TABLE(MonthYear VARCHAR(6));
INSERT INTO @Months VALUES ('Jun-19'), ('Aug-19');
SELECT Names
     , Category
     , OrderDate
     , Quantity
     , FORMAT(OrderDate,'MMM-yy') AS MonthYear
     , CASE WHEN EXISTS (SELECT 1 FROM @Months WHERE MonthYear = FORMAT(OrderDate,'MMM-yy')) THEN Quantity END AS MonthSelected
FROM #MyTable
ORDER BY OrderDate

Upvotes: 2

Sats
Sats

Reputation: 1973

You need to declare @Months with varchar(50) to accommodate more date. Also

I have changed the query to dynamic query.

DECLARE @Months VARCHAR(50)

SET @Months = '''Jul-19'',''Aug-19'''

DECLARE @sql VARCHAR(max) = ' SELECT Names,   
           Category,    
           OrderDate,
           Quantity,
           FORMAT(OrderDate,''MMM-yy'') AS MonthYear,
           CASE WHEN  FORMAT(OrderDate,''MMM-yy'')  IN (' + @Months + ' ) THEN Quantity END AS MonthSelected    
    FROM #MyTable
    ORDER BY OrderDate'

EXECUTE (@sql)

Upvotes: 2

Hrishikesh
Hrishikesh

Reputation: 309

You can use Template like 

(case YourConditionalValue
     When X then expression
     when y then  expression
     when z then expression
else expression end)

Hope it will work for you

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You need to declare a variable as below.

 SET @Months = '''Jun-19'',''Aug-19'''

You can use string_split():

(CASE WHEN FORMAT(OrderDate,'MMM-yy') IN (SELECT value FROM STRING_SPLIT(@Months, ','))
      THEN Quantity
 END) AS MonthSelected

Upvotes: 4

Related Questions