Victor Hugo Schmidt Victor Hugo Schmidt - 1 month ago 21
SQL Question

SQL - WHERE with CASE statement

SELECT TOP 1
CostValue
FROM
[~client_table~].[dbo].[CostRules] AS CostRule
WHERE
(CASE
WHEN DATALENGTH(CostRule.ModelName) = 0
THEN
CostRule.Type = 1
AND CostRule.Manufacturer = Printer.ManufacturerId
AND CostRule.ColorType = 1
ELSE
CostRule.Type = 2
AND CostRule.ModelName = Printer.ModelName
AND CostRule.ColorType = 1
END
)
) AS MonoCost


I want to define my where statement depending on the datalength of
CostRule.ModelName
. But i got an error:
Incorrect syntax near '='
. in
CostRule.Type = 1
and i got a error in the
ELSE
statement.

Answer Source

Must be like this:

...
WHERE
  (DATALENGTH(CostRule.ModelName) = 0
    AND CostRule.Type = 1
    AND CostRule.Manufacturer = Printer.ManufacturerId
    AND CostRule.ColorType = 1)
  OR
  (DATALENGTH(CostRule.ModelName) != 0
    AND CostRule.Type = 2
    AND CostRule.ModelName = Printer.ModelName
    AND CostRule.ColorType = 1)

The CASE-style from your query cannot work.