How to use if else in where condition?

Lora 200 Reputation points
2023-11-27T03:23:36.13+00:00

I need to write a query with below logic:

SELECT * FROM TABLE
WHERE 
IF(@Val IS NUMBER VALUE)
THEN ID = @Val
IF(@Val IS VARCHAR VALUE)
THEN [NAME] LIKE %@Val%

Could I use if else in the Where clause? Or case when is needed?

Any suggestions?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,269 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,571 Reputation points
    2023-11-27T03:36:09.4933333+00:00

    Hi @Lora

    If I understand right, you may not need to use if else here.

    Just modify the where condition like this:

    DECLARE @Val VARCHAR(20) 
    
    SELECT * FROM TABLE
    WHERE (IsNumeric(@Val) = 1 AND CAST(ID AS VARCHAR) = @Val)
       OR (IsNumeric(@Val) = 0 AND [NAME] LIKE '%' + @Val + '%')
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

0 additional answers

Sort by: Most helpful