Assigning a variable conditionally inside a SELECT statement on SQL SERVER

Question

I am attempting to write a SELECT statement on SQL Server that displays items based on a condition, which I provide in the subsequent WHERE clause.

Here is a simplified version of what I am attempting to write.

DECLARE @tmpvar varchar(5)
....

SELECT @tmpvar,
....
FROM some_table
WHERE 
....
and @tmpvar =
      CASE WHEN (some condition here)
             THEN 'YES'
             ELSE 'NO'
      END

The code above executes successfully, but when I check the value of @tmpvar, it remains unassigned.

I'd like to know if there is a mistake in the syntax I am using. If so, what would be the correct way of assigning the variable inside the SELECT statement using the given condition? Prior apologies if this question is redundant.

Answer 1

You can't assign in the WHERE clause. You can in the SELECT clause:

DECLARE @tmpvar varchar(5)
....

SELECT @tmpvar =
      CASE WHEN (some condition here)
             THEN 'YES'
             ELSE 'NO'
      END,
....
FROM some_table
WHERE 
....

But only if you're not also attempting data retrieval at the same time. Also, it's pretty poorly defined which row(s) will influence the result if there's more than 1 row in the result set, unless you have an ORDER BY clause:

If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

---

As you may have noticed, SQL uses = for assignment, comparison and introducing aliases. Which one is determined by the position that the = appears in. In the WHERE clause, = can only be comparison.