Сохраненная процедура с дополнительными параметрами "ГДЕ"

У меня есть форма, в которой пользователи могут указывать различные параметры для обработки некоторых данных (статус, дата и т.д.).

Я могу создать запрос, который:

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.

Ответы

Ответ 1

Один из самых простых способов сделать это:

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-инъекции.

Ответ 2

Создайте свою процедуру следующим образом:

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 параметрами, всеми параметрами или любыми # параметрами.

Ответ 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)

Ответ 4

Вы можете сделать что-то вроде

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

Ответ 5

Если вы хотите избежать динамического наращивания строк SQL (чего часто лучше всего избегать), вы можете сделать это в хранимых процедурах, сравнивая каждый критерий в своем месте, где указано значение по умолчанию, которое равнозначно "игнорировать". Например:.

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)

Ответ 6

Читаемый и поддерживаемый способ сделать это (даже применимый с JOIN/APPLY):

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

Однако это плохая идея на большинстве больших таблиц (даже больше с использованием JOIN/APPLY), так как ваш план выполнения не будет игнорировать значения NULL и генерирует массивную лазейку производительности (например: сканирование всей таблицы, ищущей значения NULL).

Обходной путь в SQL Server - это использование параметров WITH (RECOMPILE) в вашем запросе (доступно с SQL Server 2008 SP1 CU5 (10.0.2746)).

Лучший способ реализовать это (разумный результат) - использовать блок IF... ELSE, по одному для каждой комбинации. Возможно, это изнурительно, но у вас будут лучшие результаты, и это не имеет значения для настроек вашей базы данных.

Если вам нужна дополнительная информация, вы можете найти KM. answer здесь.