Conditional Where Clause in SQL Query

Question

I have developing the MVC application for generating the report. I have provided many search option like below

Customer id
Customer name
Customer E-mail
State
Country 

User 1:

If the some user will give inputs to only some Values like

Customer id = 1
Customer name = A

By default other parameters are passed as null to the stored procedure.

Customer E-mail
State
Country 

User 2:

If the some user will give inputs to only some values like

Customer [email protected]

By default other parameters are passed as null to the stored procedure.

Customer id
Customer name
State
Country 

How can i use the where clause in the SQL query in the stored procedure. Can we do it like below

string qry = select * from table_name where status != d

if (@customerID!=null)
    qry = qry + "and customer_id=@customerID"
if (@customerName!=null)
    qry = qry + "and customer_name=@customerName"

Please let me the best approach on this.

Thanks, Velu

Answer 1

If you are creating dynamic SQL then you can do just like you are above:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =  'SELECT * FROM TABLE '
if (@customerID IS NOT NULL)
    SQL = SQL + " AND customer_id = @customerID"

Or another option is to handle it like

SELECT *
FROM TABLE
WHERE (@customerID IS NULL OR customer_id = @customerID)

I prefer the second as it is utilizing parametrized variable. First example needs to take into consideration malicious input far more intensely.

Answer 2

You could do dynamic SQL, but a simpler method is:

WHERE (ISNULL(@param1,1) = 1 OR [col1] = @param1)
    AND (ISNULL(@param2,1) = 1 OR [col2] = @param2)
    AND ...