CASE statement on numeric value when NULL fails

Question

Hello all,

I'm using the following case statement in a select statement but getting the incorrect results:

CASE @Quantity
	WHEN NULL THEN 'NO'
	ELSE 'YES'
END

Quantity is a numeric(18,2).  Whether @Quantity is NULL or not, I always get the non-NULL result, 'YES'.  Any ideas what I'm doing wrong?

Answer 1

declare @Quantity numeric(18,2)=NULL


SELECT CASE When @Quantity IS NULL THEN 'NO'
	ELSE 'YES'
END

Answer 2

>> I'm using the following CASE statement [sic] in a SELECT statement but getting the incorrect results: <<

CASE is an expression and not a statement. 

CASE WHEN @in_something_qty IS NULL
     THEN 'NO' ELSE 'YES' END 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

 The CASE expression has two formats:

 Simple CASE Expression: compares an expression to a set of simple expressions to determine the result.
Syntax
CASE input_expression
      WHEN when_expression THEN result_expression [ ...n ]
      [ ELSE else_result_expression ]
END
 Searched CASE Expression: evaluates a set of Boolean expressions to determine the result.

Syntax
CASE
      WHEN Boolean_expression THEN result_expression [ ...n ]
      [ ELSE else_result_expression ]
END

a good referance with example:http://cybarlab.blogspot.com/2013/02/sql-case-statementexpression.html

Hope it will help you.

Thanks n regard