SQL查询中的条件Where子句

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

我已经开发了用于生成报告的MVC应用程序。我提供了很多搜索选项，如下所示

Customer id
Customer name
Customer E-mail
State
Country

User 1:

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

默认情况下，其他参数作为null传递给存储过程。

Customer E-mail
State
Country

User 2:

用户2:

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

如果某些用户只给一些值输入，比如

Customer E-mail=xtz@gmail.com

客户电子邮件= xtz@gmail.com

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

默认情况下，其他参数作为null传递给存储过程。

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

如何在存储过程中的SQL查询中使用where子句。我们可以在下面做吗?

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

谢谢,礁泻湖

5 个解决方案

#1

Everything you want to know about this topic, and more: Dynamic Search Conditions in T-SQL

您想了解的关于这个主题的所有内容，以及更多:T-SQL中的动态搜索条件

#2

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

如果您正在创建动态SQL，那么您可以像上面一样:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =  'SELECT * FROM TABLE '
if (@customerID != 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.

我更喜欢第二个，因为它使用的是参数化变量。第一个例子需要更强烈地考虑恶意输入。

#3

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

可以使用动态SQL，但更简单的方法是:

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

#4

Take a look at Dynamic Search Conditions in T-SQL it will give you several ways with explanations which ones to use

看看T-SQL中的动态搜索条件，它将为您提供几种解释使用哪些搜索条件的方法

#5

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

您必须将所有变量作为参数传递到SP中，然后在其中执行逻辑。

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

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

#1