代码之家 › 专栏 › 技术社区 › pistacchio

具有可选“WHERE”参数的存储过程

oracle sql-server mysql sql

pistacchio · 技术社区 · 16 年前

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

我可以生成以下查询:

SELECT * FROM table WHERE:
status_id = 3
date = <some date>
other_parameter = <value>

WHERE 是可选的(我可以使用 status = 3 ,或包含 date = 10/10/1980 ,或包含 status = 3 AND date = 10/10/1980 等等)。

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

MySQL、Oracle和SQLServer。

6 回复 | 直到 11 年前

ChrisLively 16 年前

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注入的另一个安全问题。

Eppz 16 年前

按照以下方式创建您的过程:

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个参数、所有参数或任意参数执行过程。

ForceMagic chooselife 12 年前

这是我使用的样式:

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)

Community CDub 8 年前

一种可读且可维护的方法(甚至可用于JOIN/APPLY):

where 
      (@parameter1 IS NULL OR your_condition1)
  and (@parameter2 IS NULL OR your_condition2) 
-- etc

然而,对于大多数大型表来说,这是一个坏主意(甚至更多地使用JOIN/APPLY),因为您的执行计划不会忽略空值,并会产生巨大的性能漏洞(例如:扫描所有表以搜索空值)。

SQL Server中的一种迂回方式是在查询中使用(重新编译)选项(从SQL 2008 SP1 CU5(10.0.2746)开始提供)。

实现这一点(性能方面)的最佳方法是在以下情况下使用。。。ELSE块,可能的每个组合一个。也许这会让人筋疲力尽,但您将拥有最好的性能,而这与您的数据库设置无关。

如果您需要更多详细信息,可以查找KM。答复 here

kemiller2002 16 年前

你可以这样做

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

JonoW 16 年前

如果您想避免动态构建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)