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

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 WHERE is 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/1980 etc.).

等等，每个选项都是可选的(我可以选择所有具有status = 3的行，或者所有具有date = 10/10/1980的行，或者所有具有status = 3和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.

我正在处理各种DB，比如:MySQL、Oracle和SQLServer。

6 个解决方案

#1

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

#2

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 params、所有params或任何# params执行过程。

#3

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)

#4

You can do something like

你可以做一些类似的事情

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

#5

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)来说，这不是一个好主意，因为您的执行计划不会忽略空值并产生大量的性能漏洞(例如:扫描所有表搜索空值)。

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中的一种迂回方式是在查询中使用(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。答案在这里。

#6

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字符串(这通常是最好避免的)，您可以在存储的procs中通过比较where claused中的每个critera和默认值(这等于“忽略”)来实现这一点。例如:

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)

#1