Question about case statement

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'开发者_如何学JAVA
        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 ??

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.

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

Why are you using Case when you have a table that you can join?

SELECT Product.*, DisplayType.Type
FROM Product INNER JOIN DisplayType ON Product.display_id=DisplayType.display_id

You didn't specify the name of the column that contains 'LED', 'LCD', 'PDP' so if it's different, you'll have to change that part.

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