Как использовать необязательные параметры в хранимой процедуре T-SQL?
Я создаю хранимую процедуру для поиска в таблице. У меня есть много разных полей поиска, все из которых являются необязательными. Есть ли способ создать хранимую процедуру, которая будет обрабатывать это? Допустим, у меня есть таблица с четырьмя полями: ID, FirstName, LastName и Title. Я мог бы сделать что-то вроде этого:
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
FirstName = ISNULL(@FirstName, FirstName) AND
LastName = ISNULL(@LastName, LastName) AND
Title = ISNULL(@Title, Title)
END
Такого рода работы. Однако он игнорирует записи, в которых FirstName, LastName или Title имеют значение NULL. Если заголовок не указан в параметрах поиска, я хочу включить записи, где заголовок равен NULL - то же самое для FirstName и LastName. Я знаю, что мог бы сделать это с помощью динамического SQL, но я бы хотел этого избежать.
3 ответа
Динамическое изменение результатов поиска, основанное на заданных параметрах, является сложной задачей, и выполнение этого одного способа над другим, даже с очень незначительной разницей, может иметь серьезные последствия для производительности. Ключ состоит в том, чтобы использовать индекс, игнорировать компактный код, игнорировать беспокойство о повторяющемся коде, вы должны составить хороший план выполнения запроса (использовать индекс).
Прочтите это и рассмотрите все методы. Ваш лучший метод будет зависеть от ваших параметров, ваших данных, вашей схемы и вашего фактического использования:
Условия динамического поиска в T-SQL, автор: Эрланд Соммарског
Проклятие и благословения динамического SQL, автор: Эрланд Соммарског
Если у вас есть подходящая версия SQL Server 2008 (SQL 2008 SP1 CU5 (10.0.2746) и более поздние версии), вы можете использовать этот небольшой трюк, чтобы фактически использовать индекс:
Добавьте OPTION (RECOMPILE) в ваш запрос, см. статью Эрланда , и SQL Server разрешит ИЛИ изнутри (@ LastName IS NULL OR LastName = @LastName) до того, как будет составлен план запроса создается на основе значений локальных переменных во время выполнения, и можно использовать индекс.
Это будет работать для любой версии SQL Server (вернет правильные результаты), но будет включать ОПЦИЯ (RECOMPILE), только если вы используете SQL 2008 SP1 CU5 (10.0.2746) и более поздних версий. OPTION (RECOMPILE) перекомпилирует ваш запрос, только указанная версия перекомпилирует его на основе текущих значений локальных переменных во время выполнения, что даст вам наилучшую производительность. Если не в этой версии SQL Server 2008, просто оставьте эту строку отключенной.
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR (FirstName = @FirstName))
AND (@LastName IS NULL OR (LastName = @LastName ))
AND (@Title IS NULL OR (Title = @Title ))
OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
END
Вы можете сделать это в следующем случае,
CREATE PROCEDURE spDoSearch
@FirstName varchar(25) = null,
@LastName varchar(25) = null,
@Title varchar(25) = null
AS
BEGIN
SELECT ID, FirstName, LastName, Title
FROM tblUsers
WHERE
(@FirstName IS NULL OR FirstName = @FirstName) AND
(@LastNameName IS NULL OR LastName = @LastName) AND
(@Title IS NULL OR Title = @Title)
END
однако, в зависимости от данных, иногда лучше создать динамический запрос и выполнить их.
Расширьте свое условие WHERE :
WHERE
(FirstName = ISNULL(@FirstName, FirstName)
OR COALESCE(@FirstName, FirstName, '') = '')
AND (LastName = ISNULL(@LastName, LastName)
OR COALESCE(@LastName, LastName, '') = '')
AND (Title = ISNULL(@Title, Title)
OR COALESCE(@Title, Title, '') = '')
i. е. комбинировать разные случаи с логическими условиями.