Görkem Yazıcı Görkem Yazıcı - 15 days ago 9
SQL Question

Can I select the variable in where clause in MSSQL?

I want to select the variable in where clause according to query result.

Select table1.*, table2.color, table3.type
from table1
inner join table2 on table1.ID=table2.table1Id
inner join table3 on table1.ID=table3.table1Id
where table3.type = @x OR table3.type = @y

| productName | Category | color | type |
| abc | electronics | blue | x |
| abc | electronics | blue | y |
| def | electronics | red | x |


This query can returns duplicate result because product can has two types. I want to select the variable in where clause. For example, I want to get products that have type of @y but if product's @y type is not exist I want to return @x type. I don't want first abc row in example result. Can you please help about my query?

Answer
Select table1.*, table2.color, table3.type from table1
inner join table2 on table1.ID=table2.table1Id inner join table3 on table1.ID=table3.table1Id where table3.type = CASE WHEN LEN(@x) > 0 THEN @x WHEN LEN(@y) > 0 THEN @y END