Click here to Skip to main content
15,314,303 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This is my code:
How to use IF statement inside a where closure,Plese help me for re-arrange these code

SQL
WHERE
	IF IsNumeric(@OrderNumber) = 1
		OrderNumber = @OrderNumber
	ELSE
	        BillId= @OrderNumber

What I have tried:

WHERE
   IF IsNumeric(@OrderNumber) = 1
		OrderNumber = @OrderNumber
	ELSE
	        BillId= @OrderNumber
Posted
Updated 5-Apr-16 23:43pm

1 solution

Use CASE:
SQL
WHERE --Value based
    CASE IsNumeric(@OrderNumber)
    WHEN 1 THEN OrderNumber = @OrderNumber
    ELSE BillId= @OrderNumber
    END


SQL
WHERE --boolean based
    CASE 
    WHEN IsNumeric(@OrderNumber) = 1 THEN OrderNumber = @OrderNumber
    ELSE BillId= @OrderNumber
    END


Hope that helps ^_^
Andy


UPDATE: Actually, as pointed out, this won't work. The case can only return a value, not be used as a logical condition.

It can be used in this case as so:
SQL
WHERE --boolean based
    CASE 
    WHEN IsNumeric(@OrderNumber) = 1 THEN OrderNumber
    ELSE BillId
    END = @OrderNumber


or, just as valid would be to use an OR clause:
SQL
WHERE --
    ( IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
    OR
    ( IsNumeric(@OrderNumber) = 0 AND BillId= @OrderNumber)
   
v2
Comments
MAGuru 5-Apr-16 8:26am
   
@Andy Lannng It shows incorrect syntax near =
Andy Lanng 5-Apr-16 8:27am
   
Doh! - Ok - We can get this to work - I'll update it
MAGuru 5-Apr-16 8:29am
   
ok ty
Andy Lanng 5-Apr-16 8:49am
   
Sorry for delay. Lost internet for a moment.
Solution Updated ^_^

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900