Conditional Where Clause in SQL Query

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 E-mail=xtz@gmail.com

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

12
задан FrustratedWithFormsDesigner 3 May 2011 в 18:41
поделиться