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

NickD · Accepted Answer · 2019-11-03 21:27:04Z

14

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.

edited Nov 3, 2019 at 21:27

NickD

6,5201 gold badge27 silver badges43 bronze badges

answered May 3, 2011 at 18:44

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SQLMenace Over a year ago

I prefer the second as it is utilizing parametrized variable not optimized, see blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/… you can use sp_executesql to build a safe string

Dustin Laine Over a year ago

I agree, but I have also not had trouble with it on fairly large volume applications. So the balance between security/performance has to be weighed. All in all I do agree it is not the best execution plan.

SQLMenace Over a year ago

not to nitpick but this if (@customerID != null) won't catch nulls, you need if (@customerID IS NOT null)

Christo · Accepted Answer · 2011-05-03 18:50:16Z

5

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 ...

answered May 3, 2011 at 18:50

Christo

9,1872 gold badges24 silver badges16 bronze badges

2 Comments

SQLMenace Over a year ago

And you just flushed your indexes down the toilet with that non SARGable query

Christo Over a year ago

@SQLMenace, @Remus: interesting how you divine a performance problem with a db schema and software you've not seen, and have not run a query against. Anyone knows that this is best handled peformance-wise by a purpose-designed stored procedure, but I felt that was a bit beyond the context of the question. The OP was comparing to null, which sets the level of the discussion, don't you think?

Chris Kooken · Accepted Answer · 2011-05-03 18:42:28Z

0

you'll have to pass all the variables as parameters into the SP and then do your logic in there.

SqlCommand cmd  = new SqlCommand("STORED_PROC_NAME", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@CustomerID", custId));
var rdr = cmd.ExecuteReader();

answered May 3, 2011 at 18:42

Chris Kooken

34.1k15 gold badges93 silver badges130 bronze badges

Collectives™ on Stack Overflow

Conditional Where Clause in SQL Query

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related