I’ve seen quite a few posts from people asking how to do something like this:

SELECT
  a.ID
, IF a.MyChar = 'A' THEN 'Success'
  ELSE 'Fail'
FROM MyTable a

Of course, that doesn’t work in T-SQL, and you’ll get something like this:

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword ‘IF’.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword ‘THEN’.

There’s not IIF, no IF( x, then y, else z) construct. There is an IF … ELSE statement, but it’s use in code flows as a control statement such as

DECLARE @i CHAR(1)
SELECT @i = mychar FROM MyTable
IF @i = 'A'
  SELECT 'Success'
ELSE
  SELECT 'Fail'
  

Instead we have a CASE statement, which is designed to give you multiple choices. In the example above, I’d write:

SELECT
  a.ID
, CASE WHEN a.MyChar = 'A' THEN 'Success'
  ELSE 'Fail'
  END
FROM MyTable a

I can even add multiple “WHEN” clauses if I want:

SELECT
  a.ID
, CASE
    WHEN a.MyChar = 'A' THEN 'Success'
    WHEN a.MyChar = 'B' THEN 'Close'
    WHEN a.MyChar = 'C' THEN 'Far'
    ELSE 'Fail'
  END
FROM MyTable a

Let your developers know that when they are looking for an inline IF type of logical statement, T-SQL gives them CASE instead.

Filed under: Blog Tagged: syndicated, T-SQL