0

Sorry if it is asked before, I really searched but could not find.

What I am trying to do is to assign 'null' or 'not null' to a varchar variable depending on a parameter in MS SQL. On the first one, The user would only see the description if he/she selects 1 as index. On the second one, the user would see the description if the year column is larger than 2005 for a record. The code I am trying is below but not working. Can someone help me on this logic please? Really appreciated.

Thanks.

--- First one---
Declare @index int;
Select * from Cars
Where Description is (case when @index = 1 then ‘not null’ else ‘null’ end)

---Second one----
Select * from Cars
where Description is (case when Cars.Year > 2005 then ‘not null’ else ‘null’ end)
Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • you are missing an `end` on each `case` statement: should be `case when then else end`. Also, this should be `where description = ` or where description like `, not `where description is ` – Zohar Peled Mar 01 '16 at 15:14
  • updated. thanks. but this is not the solution, just copy paste error:( – Eray Balkanli Mar 01 '16 at 15:15

1 Answers1

3

First one:

SELECT * 
FROM Cars
WHERE (Description IS NOT NULL) AND (@index = 1) 
       OR
      (Description IS NULL) AND (@index <> 1)

Second one:

SELECT * 
FROM Cars
WHERE (Description IS NOT NULL) AND (Year > 2005)
       OR 
      (Description IS NULL) AND (Year <= 2005)

CASE in SQL is an expression and cannot be used as a flow control statement.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98