• Register
0 votes
21 views
1.9k points

Please log in or register to answer this question.

1 Answer

0 votes

If  condition in where clause in SQL

If you want to write a stored procedure using IF clause within a WHERE clause in MS SQL, similar to
WHERE
    IF IsNumeric(@OrderNumber) = 1
        OrderNumber = @OrderNumber
    ELSE
        OrderNumber LIKE '%' + @OrderNumber + '%'

You can use a CASE statement. Use CASE as follows:

WHERE OrderNumber LIKE
  CASE WHEN IsNumeric(@OrderNumber) = 1 THEN 
    @OrderNumber 
  ELSE
    '%' + @OrderNumber
  END

You can do this without any IF or CASE:

WHERE 
   (IsNumeric(@OrderNumber) AND
      (CAST OrderNumber AS VARCHAR) = (CAST @OrderNumber AS VARCHAR)
 OR
   (NOT IsNumeric(@OrderNumber) AND
       OrderNumber LIKE ('%' + @OrderNumber))

It is a common technique in a WHERE clause. If you want to apply an "IF" logic in the WHERE clause. If you need to apply some "IF" logic in the WHERE clause, you need to add an extra condition with a Boolean AND to the section where it needs to be applied.

Another solution:

WHERE
    (IsNumeric(@OrderNumber) = 1 AND OrderNumber = @OrderNumber)
OR (IsNumeric(@OrderNumber) = 0 AND OrderNumber LIKE '%' + @OrderNumber + '%')

It is an excellent idea to do it in SQL like this:

Use CASE combined with boolean operators:

WHERE
    OrderNumber = CASE 
        WHEN (IsNumeric(@OrderNumber) = 1)
        THEN CONVERT(INT, @OrderNumber)
        ELSE -9999 -- Some numeric value that cannot exist in the column
    END
    OR 
    FirstName LIKE CASE
        WHEN (IsNumeric(@OrderNumber) = 0)
        THEN '%' + @OrderNumber
        ELSE ''
    END

You can use IF outside the SELECT.

IF (IsNumeric(@OrderNumber)) = 1
BEGIN
    SELECT * FROM Table
    WHERE @OrderNumber = OrderNumber
END ELSE BEGIN
    SELECT * FROM Table
    WHERE OrderNumber LIKE '%' + @OrderNumber
END

I hope it helps!

3.4k points

Related questions

0 votes
1 answer 3 views
3 views
Problem I do not understand this problem, "If condition in where clause in sql server?"
asked Apr 7 salim1 60.6k points
0 votes
1 answer 1 view
1 view
Problem: And condition in where clause in codeigniter.
asked Jul 10 Shima 106k points
0 votes
1 answer 11 views
11 views
Problem: Please assist me in resolving this issue: there should be one condition within the where clause for each pair of tables being joined
asked Apr 6 tuhin1 48.9k points
0 votes
1 answer 40 views
40 views
Problem: I’m new in data frame. I received this error message: Error in if (condition) { : missing value where TRUE/FALSE needed or Error in while (condition) { : missing value where TRUE/FALSE needed What does it mean, and how do I prevent it?
asked Mar 10 Sabbir Hossain 14.6k points
0 votes
1 answer 3 views
3 views
Problem: Difference between having and where clause in sql.
asked Jun 29 Rada Gracelynn 91.8k points
0 votes
1 answer 7 views
7 views
Problem: Sql case statement in where clause multiple values.
asked Jun 28 akhi 106k points
0 votes
1 answer 6 views
6 views
Problem: I am looking a solution for this problem : Oracle sql case statement in where clause.
asked Jun 1 Irenka Kiri 85.5k points
0 votes
1 answer 16 views
16 views
Problem: Tell me the difference between where and having clause in sql server.
asked May 10 Junia Phoebe 83.8k points
0 votes
1 answer 20 views
20 views
Problem: Please can anyone tell me the right procedure to do it? Sql server row_number in where clause
asked Apr 27 Ifra 43.4k points
0 votes
1 answer 10 views
10 views
Question: How would i write this sql statement without a hard coded value? resultSet = statement .executeQuery("select * from myDatabase.myTable where name = 'john'"); // this works rather have something like: String name = "john"; resultSet = statement . ... ); // Unknown column 'john' in 'where clause' at // sun.reflect.NativeConstructorAccessorImpl.newInstance0...etc... Thanks in advance.
asked Apr 13 Atik03 26.5k points