带有可选"WHERE"参数的存储过程

我有一个表单,用户可以指定各种参数来挖掘一些数据(状态,日期等).

我可以生成一个查询:

SELECT * FROM table WHERE:
status_id = 3
date = 
other_parameter = 

每个WHERE都是可选的(我可以选择所有行status = 3,或所有行date = 10/10/1980,或所有行status = 3 AND date = 10/10/1980等).

给定大量参数,都是可选的,构成动态存储过程的最佳方法是什么？

我正在研究各种数据库,例如:MySQL,Oracle和SQLServer.

1> NotMe..：

---

完成此任务的最简单方法之一:

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

这完全消除了动态sql,并允许您搜索一个或多个字段.通过消除动态sql,您删除了有关sql注入的另一个安全问题.

---

请记住,根据您的RDBMS以及它如何缓存查询计划,您可能无法使用此方法获得最佳性能.

---

另一个危险是你以某种方式设法为所有内容传递NULL,然后每一行都返回.我在应用程序中看到这种情况,它试图向客户端发送100万行.

---

这不允许所有参数都是可选的.在此示例中,必须传入@status_id.即使它为null,也必须传入null才能执行此操作.

2> Eppz..：

---

像这样创建你的程序:

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

这将允许您使用0参数,所有参数或任何#srms执行该过程.

3> 小智..：

---

这是我使用的风格:

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) 

神谕

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)