Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

The IF Statement in a T-SQL Query

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

Comments

Posted by nlaslett on 10 July 2011

Um...  Why use a searched case statement when a simple case statement will suffice?

CASE a.MyChar

WHEN 'A' THEN 'Success'

WHEN 'B' THEN 'Close'

WHEN 'C' THEN 'Far'

ELSE 'Fail'

END

Rookie mistake, Steve!  :-)

Leave a Comment

Please register or log in to leave a comment.