Question about case statement

Question

I am studying searching part on the SQL but i am getting errors

What I did was... first, I have a display type table

display_id  |
1           | LED
2           | LCD
3           | PDP

Second, I make select query.

SELECT * FROM Product 
    WHERE
    display_id = 
    CASE @display_type 
        WHEN  1  then 'LED'
        WHEN  2 then 'LCD'
        WHEN  3 then 'PDP'
        END

if display id is 1 then i would like show the ' LED ' for the result of query.

however, I am getting error

Msg 245, Level 16, State 1, Procedure search_item, Line 18
Conversion failed when converting the varchar value 'LED' to data type int.

i tried to use convert but it is not working.

does anybody know the solution of this problem ??

Answer 1

You're mixing data types in the WHERE clause. display_id appears to be and integer and you're comparing it to 'LED', 'LCD' or 'PDP, depending on the result of the CASE statement.

Answer 2

You can convert @display_type to a string for display purposes by moving your Case Statement to the Select clause.

SELECT
  CASE WHEN convert(int, @display_type) = 1 then 'LED'
       WHEN convert(int, @display_type) = 2 then 'LCD'
       WHEN convert(int, @display_type) = 3 then 'PDP'
  END as DisplayType,
  *
FROM
  Product

If all you are after is a list of Products with a certain display type....

SELECT
  *
FROM
  products as P
  inner join MyDisplayTable as DISP on DISP .display_id = P.display_id
WHERE
  DISP.Display_Type = @display_type

• Assumes the "name" column of your displays table is Display_Type
• Assumes Product table has a display_id column (hopefully with a foreign-key to the display table's primary key