MySQL 带有可选“WHERE”参数的存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/697671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored Procedure with optional "WHERE" parameters
提问by pistacchio
I have a form where users can specify various parameters to dig through some data (status, date etc.).
我有一个表单，用户可以在其中指定各种参数来挖掘一些数据（状态、日期等）。
I can produce a query that is:
我可以生成一个查询：
SELECT * FROM table WHERE:
status_id = 3
date = <some date>
other_parameter = <value>
etc. Each WHEREis optional (I can select all the rows with status = 3, or all the rows with date = 10/10/1980, or all the rows with status = 3 AND date = 10/10/1980etc.).
等等。每个WHERE都是可选的（我可以选择所有带有 的行status = 3，或者所有带有 的行date = 10/10/1980，或者所有带有status = 3 AND date = 10/10/1980等等的行）。
Given a large number of parameters, all optional, what is the best way to make up a dynamic stored procedure?
给定大量参数，都是可选的，组成动态存储过程的最佳方法是什么？
I'm working on various DB, such as: MySQL, Oracle and SQLServer.
我正在处理各种数据库，例如：MySQL、Oracle 和 SQLServer。
回答by NotMe
One of the easiest ways to accomplish this:
实现此目的的最简单方法之一：
SELECT * FROM table
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))
etc. This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.
等等。这完全消除了动态sql，并允许您搜索一个或多个字段。通过消除动态 sql，您消除了关于 sql 注入的另一个安全问题。
回答by Eppz
Create your procedure like this:
像这样创建你的程序：
CREATE PROCEDURE [dbo].[spXXX]
@fromDate datetime = null,
@toDate datetime = null,
@subCode int = null
as
begin
set NOCOUNT ON
/* NOCOUNT limits the server feedback on select results record count */
SELECT
fields...
FROM
source
WHERE
1=1
--Dynamic where clause for various parameters which may or may not be passed in.
and ( @fromDate is null or [dateField] >= @fromDate)
and ( @toDate is null or [dateField] <= @toDate)
and ( @subCode is null or subCode= @leaveTypeSubCode)
order by fields...
This will allow you to execute the procedure with 0 params, all params, or any # of params.
这将允许您使用 0 个参数、所有参数或任意 # 个参数执行该过程。
回答by Rich
This is the style I use:
这是我使用的样式：
t-sql
t-sql
SELECT *
FROM table
WHERE
status_id = isnull(@status_id ,status_id)
and date = isnull(@date ,date )
and other_parameter = isnull(@other_parameter,other_parameter)
oracle
甲骨文
SELECT *
FROM table
WHERE
status_id = nval(p_status_id ,status_id)
and date = nval(p_date ,date )
and other_parameter = nval(p_other_parameter,other_parameter)
回答by Lostblue
A readable and maintainable way to do it (even usable with JOIN/APPLY) :
一种可读且可维护的方法（甚至可以与 JOIN/APPLY 一起使用）：
where
(@parameter1 IS NULL OR your_condition1)
and (@parameter2 IS NULL OR your_condition2)
-- etc
However it's a bad idea on most big tables (even more using JOIN/APPLY), since your execution plan will not ignore NULL values and generates massive performance loophole (ex : scaning all a table searching for NULL values).
然而，对于大多数大表（甚至更多地使用 JOIN/APPLY）来说，这是一个坏主意，因为您的执行计划不会忽略 NULL 值并产生大量的性能漏洞（例如：扫描所有表以搜索 NULL 值）。
A roundabout way in SQL Server is to use WITH(RECOMPILE) options in your query (available since SQL 2008 SP1 CU5 (10.0.2746)).
SQL Server 中的一种迂回方式是在查询中使用 WITH(RECOMPILE) 选项（自 SQL 2008 SP1 CU5 (10.0.2746) 起可用）。
The best way to implements this (performance wise) is to use IF ... ELSE block, one for each combination possible. Maybe it's exhausting but you will have the best performances and it doesn't matter your database settings.
实现这一点的最佳方法（性能方面）是使用 IF ... ELSE 块，每个可能的组合一个。也许这很累，但您将获得最佳性能，而且您的数据库设置无关紧要。
If you need more details, you can look for KM. answer here.
如果您需要更多详细信息，可以查找 KM。在这里回答。
回答by kemiller2002
You can do something like
你可以做类似的事情
WHERE
(
ParameterA == 4 OR ParameterA IS NULL
)
AND
(
ParameterB == 12 OR ParameterB IS NULL
)
回答by JonoW
If you want to avoid dynamically building up SQL strings (which is often best avoided), you can do this in stored procs by comparing each critera in your where claused with a default value, which equates to "ignore". E.g.:
如果您想避免动态构建 SQL 字符串（通常最好避免），您可以通过将 where 子句中的每个标准与默认值进行比较，从而在存储过程中执行此操作，这相当于“忽略”。例如：
select * from Table where
(@Col1 IS NULL OR Col1 = @Col1) /*If you don't want to filter in @col, pass in NULL*/
AND
(@Col2 IS NULL OR Col2 = @Col2)
