Using IF in SELECT 1

[AgentM]

Can I use "IF" in a Select stmt e.g.

SELECT Db1.Value1, IF (Db1.Value2 != 'good','bad','ok') FROM Database1 as Db1

I am getting a syntax error on IF
any help appreciated.

 

[gmmastros]

If is used to control the flow of logic. Case/When is used for data. So....

SELECT Db1.Value1, 
       Case When Db1.Value2 <> 'good'
            Then 'bad'
            Else 'ok'
            End As SomeAliasName
FROM   Database1 as Db1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I should also mention (not that it applies in your example) that it is critically important to be consistent with the output data type for each branch of execution when you use the Case/When structure.

Please read this to understand why:

http://blogs.lessthandot.com/index....erver/sql-server-case-when-data-type-problems

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AgentM]

Thank you.
So IF cannot be used at all in SELECT ?h

 

[bborissov]

Nope,
You can't

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

No. Not in SQL Server. Besides, the Case/When construct gives you more flexibility because there can be multiple "if's". Example

Select Case Status When 1 Then 'ONE'
                   When 2 Then 'TWO'
                   When 3 Then 'Three'
                   Else 'Unknown'
                   End As StatusDescription

You can use IF in a multi-statement query like this:

If Exists(Select * From Table Where Status = 1)
  Begin
    Select 'We found it'
  End
Else
  Begin
    Select 'It's not here'
  End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom