Search code examples
sqlasp.net-mvc-4caseprepared-statement

Conditional Statements in SQL query to check multiple values (list) passed as parameter

Requirement:

  1. When a Product ID or multiple Product IDs is/are passed, all the information of the Product (Product Name, Price, Quantities Available, Description, etc) should be fetched for the passed Product ID/Product IDs.

  2. When Product ID is not passed (null), then Product Information of all the available Product IDs should be fetched.

In my application, Product_Id is a List<string>. I'm passing this list to my SQL Select query as a parameter. If this list is null I have to select all the Product IDs from Product table. If its not null, then I have to fetch only those Product IDs that are in the list.

The query I'm trying to write for the above requirement is given below.

SELECT * FROM PRODUCT WHERE PRODUCT_ID IN (?)
Solution

  • How about

    SELECT * FROM PRODUCT WHERE @param IS NULL OR PRODUCT_ID IN (@param)

    ?