TSQL LAB 2 – создание настраиваемых выражений WHERE

В запросах и хранимых процедурах, служащих для создания отчетов, зачастую приходится использовать выражение WHERE с условиями. Это нужно потому, что фильтр следует накладывать, только если пользователь указал значение одного или нескольких фильтров.

Люди используют разные способы создания настраиваемых выражений WHERE. Одни создают и используют динамические строки запроса. Однако другие не согласны использовать динамические запросы. Одна из причин этого состоит в том, что динамические запросы не выигрывают от кэширования планов исполнения. Цель этой статьи не в том, чтобы выяснить, какой из подходов лучше, а в том, чтобы рассмотреть разные способы использования настраиваемых выражений WHERE.

Создание и исполнение динамического запроса

Рассмотрим очень простую хранимую процедуру, создающую динамический запрос на основе значений параметров и исполняющую его:

/*
EXECUTE ProductSearch1 NULL
EXECUTE ProductSearch1 'AR'
*/
CREATE PROCEDURE ProductSearch1
(
    @ProductNumber VARCHAR(20)
)
AS
 
SET NOCOUNT ON
DECLARE @sql VARCHAR(MAX)
SET @sql = ' SELECT * FROM Production.Product '
 
IF @ProductNumber IS NOT NULL BEGIN
    SET @sql = @sql + ' WHERE ProductNumber LIKE ''' +    
        @ProductNumber + '%'''
END
 
-- используем выражение print для отладки
-- PRINT @sql
 
EXEC(@sql)

Одна из проблем этого подхода кроется в проверке ошибок во время компиляции. При изменении запросов такого типа нужно быть очень внимательным. Некоторые модификации могут привести к генерированию неверных SQL-запросов, содержащих ошибки в синтаксисе или неверные имена колонок.

Запрос становится немного сложнее, если параметров несколько. Сложность состоит в добавлении выражения WHERE. Вот новая версия запроса:

/*
EXECUTE ProductSearch2 NULL, NULL, NULL
EXECUTE ProductSearch2 'CA', NULL, NULL
EXECUTE ProductSearch2 NULL, 'Black', NULL
EXECUTE ProductSearch2 NULL, NULL, 375
EXECUTE ProductSearch2 'CA', 'Black', NULL
EXECUTE ProductSearch2 'CA', 'Black', 375
*/
CREATE PROCEDURE ProductSearch2
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
 
SET NOCOUNT ON
DECLARE @sql VARCHAR(MAX)
SET @sql = ' SELECT * FROM Production.Product '
 
-- Нужно использовать WHERE, если хотя бы один параметр
-- не равен null 
IF @ProductNumber IS NOT NULL 
    OR @Color IS NOT NULL 
    OR @ReOrderPoint IS NOT NULL 
BEGIN
    SET @sql = @sql + ' WHERE '
END
 
IF @ProductNumber IS NOT NULL BEGIN
    SET @sql = @sql + ' ProductNumber LIKE ''' 
        + @ProductNumber + '%'''
END
 
-- Решение добавить оператор AND 
-- тоже не из простых
IF @Color IS NOT NULL BEGIN
    IF @ProductNumber IS NOT NULL 
        SET @sql = @sql + ' AND '
 
    SET @sql = @sql + ' Color LIKE ''' 
        + @Color + '%'''
END
 
IF @ReOrderPoint IS NOT NULL BEGIN
    IF @ProductNumber IS NOT NULL OR @Color IS NOT NULL
        SET @sql = @sql + ' AND '
 
    SET @sql = @sql + ' ReorderPoint = ' + 
        CAST(@ReOrderPoint AS VARCHAR)
END
-- используем выражение print для отладки
--PRINT @sql
 
EXEC(@sql)

Посмотрите повнимательнее на код. Он стал сложным. Есть несколько проблем. Во-первых, это решение, включать выражение WHERE или нет. Следующая проблема – решение, использовать оператор AND или нет. Зачастую перед операторами забывают поставить пробел. Например, легко можно написать SET @sql = @sql + 'AND ' вместо SET @sql = @sql + ' AND '.

Если параметров больше, код, подобный приведенному выше, может стать чересчур сложным. Есть альтернативный способ писать такие запросы. Вот еще одна версия того же самого запроса:

/*
EXECUTE ProductSearch3 NULL, NULL, NULL
EXECUTE ProductSearch3 'CA', NULL, NULL
EXECUTE ProductSearch3 NULL, 'Black', NULL
EXECUTE ProductSearch3 NULL, NULL, 375
EXECUTE ProductSearch3 'CA', 'Black', NULL
EXECUTE ProductSearch3 'CA', 'Black', 375
*/
CREATE PROCEDURE ProductSearch3
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
 
SET NOCOUNT ON
DECLARE @sql VARCHAR(MAX)
DECLARE @where VARCHAR(MAX)
 
-- создание выражения WHERE 
SET @where = ''
IF @ProductNumber IS NOT NULL BEGIN
    SET @where = ' ProductNumber LIKE ''' 
        + @ProductNumber + '%'''
END
 
IF @Color IS NOT NULL BEGIN
    SET @where = @where + 
        CASE WHEN LEN(@where) > 0 THEN ' AND ' ELSE ' ' END +
        ' Color LIKE ''' + @Color + '%'''
END
 
IF @ReOrderPoint IS NOT NULL BEGIN
    SET @where = @where + 
        CASE WHEN LEN(@where) > 0 THEN ' AND ' ELSE ' ' END +
        ' ReorderPoint = ' + CAST(@ReOrderPoint AS VARCHAR)
END
 
SET @sql = ' SELECT * FROM Production.Product '
IF LEN(@where) > 0 BEGIN
    SET @sql = @sql + ' WHERE ' + @where 
END
 
-- используем выражение print для отладки
--PRINT @sql
EXEC(@sql)

Этот код выглядит несколько чище. Реальные преимущества проявляются, когда нужно проверять больше параметров. Нам нужно только условие IF для каждого имеющегося параметра. В конце мы конструируем финальный запрос и исполняем его.

Выражение WHERE нужно добавлять, только если переменная @where не пуста. Нужно позаботиться о двух вещах:

У меня есть друг, использующий другую версию такого запроса. Он использует "WHERE 1=1", чтобы избавиться от приведенных выше пунктов 1 и 2. Вот его версия кода:

/*
EXECUTE ProductSearch4 NULL, NULL, NULL
EXECUTE ProductSearch4 'CA', NULL, NULL
EXECUTE ProductSearch4 NULL, 'Black', NULL
EXECUTE ProductSearch4 NULL, NULL, 375
EXECUTE ProductSearch4 'CA', 'Black', NULL
EXECUTE ProductSearch4 'CA', 'Black', 375
*/
CREATE PROCEDURE ProductSearch4
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
 
SET NOCOUNT ON
DECLARE @sql VARCHAR(MAX)
DECLARE @where VARCHAR(MAX)
 
-- создание выражения WHERE 
SET @where = ''
IF @ProductNumber IS NOT NULL BEGIN
    -- просто добавляем "AND"
    SET @where = ' AND ProductNumber LIKE ''' 
        + @ProductNumber + '%'''
END
 
IF @Color IS NOT NULL BEGIN
    -- просто добавляем "AND"
    SET @where = @where +' AND Color LIKE ''' 
        + @Color + '%'''
END
 
IF @ReOrderPoint IS NOT NULL BEGIN
    -- просто добавляем "AND"
    SET @where = @where + ' AND ReorderPoint = ' 
        + CAST(@ReOrderPoint AS VARCHAR)
END
 
SET @sql = ' 
    SELECT * 
    FROM Production.Product 
    WHERE 1=1 ' + @where
 
-- используем выражение print для отладки
-- PRINT @sql
EXEC(@sql)

Здесь есть несколько интересных вещей. Прежде всего, он добавляет AND без проверки длины переменной фильтра. Во-вторых, всегда применяется выражение "WHERE". Проверять длину строки перед добавлением "AND" или "WHERE" не нужно.

Но все эти способы применимы, только если вы используете динамические запросы. Если же вы хотите обойтись без них, есть несколько способов.

SQL Injection

У приведенных выше подходов есть одна проблема. Они не устойчивы к SQL Injection. Что такое SQL Injection? Это способ причинить вред вашей БД или выполнить вредные/ненужные запросы к этой БД, передавая параметры, содержащие опасные исполняемые выражения. Рассмотрим пример.

Посмотрим, что происходит при выполнении созданной выше хранимой процедуры.

При исполнении этого кода генерируется следующий SQL:

Пока никаких проблем. Посмотрим, что случится, если выполнить этот же запрос со следующими параметрами:

При передаче этого параметра хранимая процедура попытается выполнить следующий код:

Такой запрос при исполнении удалит записи из таблицы products. Это грубый пример SQL Injection.

Использование sp_executesql

Использование sp_executesql при исполнении динамических запросов (вместо EXEC()) позволит защитить запросы практически от любого SQL Injection. Еще одно преимущество sp_executesql – многократное использование плана исполнения. Предыдущую версию можно переписать для использования sp_executesql следующим образом:

--EXECUTE ProductSearch5 'CA%', 'Black', 375
CREATE PROCEDURE ProductSearch5
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT
)
AS
 
SET NOCOUNT ON
 
DECLARE @sql NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
 
-- Создание основного запроса
SELECT @sql = '
    SELECT * 
    FROM Production.Product 
    WHERE 1=1 '
 
-- Создание списка параметров
SELECT @params = '
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10),
    @ReOrderPoint INT'
 
IF @ProductNumber IS NOT NULL 
  SELECT @sql = @sql + ' AND ProductNumber LIKE @ProductNumber '
 
IF @Color IS NOT NULL 
  SELECT @sql = @sql + ' AND Color LIKE @Color '
 
IF @ReOrderPoint IS NOT NULL 
  SELECT @sql = @sql + ' AND ReorderPoint = @ReOrderPoint '
-- исполнение запроса
EXEC sp_executesql @sql, @params, @ProductNumber, @Color, @ReorderPoint

В большинстве случаев sp_executesql предпочтительнее использовать, чем EXEC. Еще раз, в некоторых случаях EXEC удобнее. Несколько таких случаев можно найти по ссылкам в конце статьи.

Применение условия WHERE без использования Dynamic Query

Есть разные способы использования операторов сравнения. Следующий пример показывает, как использовать оператор LIKE с выражением WHERE.

/*
EXECUTE ProductSearch5 NULL, NULL
EXECUTE ProductSearch5 'CA%', NULL
EXECUTE ProductSearch5 NULL, 'Black%'
EXECUTE ProductSearch5 'CA%', 'Black%'
*/
CREATE PROCEDURE ProductSearch5
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10)
)
AS
 
SET NOCOUNT ON
 
SELECT *
FROM Production.Product 
WHERE ProductNumber LIKE ISNULL(@ProductNumber, '%')
    AND Color LIKE ISNULL(@Color, '%')

Довольно просто, не так ли? Запрос гораздо проще, чем приведенный ранее. Функция ISNULL() прекрасно работает с оператором LIKE. Если параметр пуст, при сравнении используется подстановочный знак «%». Если параметр не пуст, при сравнении используется его значение.

Я встречал людей, пишущих такие запросы с использованием CASE. Вот версия с использованием CASE:

/*
EXECUTE ProductSearch6 NULL, NULL
EXECUTE ProductSearch6 'CA%', NULL
EXECUTE ProductSearch6 NULL, 'Black%'
EXECUTE ProductSearch6 'CA%', 'Black%'
*/
CREATE PROCEDURE ProductSearch6
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10)
)
AS
 
SET NOCOUNT ON
 
SELECT *
FROM Production.Product 
WHERE 
    ProductNumber LIKE CASE 
                    WHEN @ProductNumber IS NULL THEN '%'
                    ELSE @ProductNumber END
    AND Color LIKE CASE 
                    WHEN @Color IS NULL THEN '%'
                    ELSE @Color END

По-другому это можно записать с использованием оператора OR:

/*
EXECUTE ProductSearch7 NULL, NULL
EXECUTE ProductSearch7 'CA%', NULL
EXECUTE ProductSearch7 NULL, 'Black%'
EXECUTE ProductSearch7 'CA%', 'Black%'
*/
CREATE PROCEDURE ProductSearch7
(
    @ProductNumber VARCHAR(20),
    @Color VARCHAR(10)
)
AS
 
SET NOCOUNT ON
 
SELECT *
FROM Production.Product 
WHERE 
    (@ProductNumber IS NULL OR ProductNumber LIKE @ProductNumber)
    AND
    (@Color IS NULL OR Color LIKE @Color)

CASE и OR работают с большинством операторов сравнения. А вот ISNULL() можно использовать далеко не со всеми операторами. С LIKE, =, >= или <= он работает, а с условиями типа > или < – нет. Следующая хранимая процедура показывает три разных способа создания настраиваемого выражения WHERE для оператора «=».

/*
EXECUTE ProductSearch8 NULL
EXECUTE ProductSearch8 375
*/
CREATE PROCEDURE ProductSearch8
(
    @ReorderPoint INT
)
AS
 
SET NOCOUNT ON
 
/*
Вариант 1
*/
SELECT *
FROM Production.Product 
WHERE ReorderPoint = ISNULL(@ReorderPoint,ReorderPoint)
 
/*
Вариант 2
*/
SELECT *
FROM Production.Product 
WHERE ReorderPoint = 
    CASE 
        WHEN @ReorderPoint IS NULL THEN ReorderPoint
        ELSE @ReorderPoint
    END
 
/*
Вариант 3
*/
SELECT *
FROM Production.Product 
WHERE @ReorderPoint IS NULL OR ReorderPoint = @ReorderPoint

Но если нужный нам оператор сравнения – "<", ">" или "<>", сработает только третий вариант.

/*
EXECUTE ProductSearch9 NULL
EXECUTE ProductSearch9 375
*/
CREATE PROCEDURE ProductSearch9
(
    @ReorderPoint INT
)
AS
 
SET NOCOUNT ON
 
/*
Вариант 1
This will not work if "@ReorderPoint" IS NULL
*/
SELECT *
FROM Production.Product 
WHERE ReorderPoint > ISNULL(@ReorderPoint,ReorderPoint)
 
/*
Вариант 2
не сработает в случае "@ReorderPoint" IS NULL
*/
SELECT *
FROM Production.Product 
WHERE ReorderPoint > 
    CASE 
        WHEN @ReorderPoint IS NULL THEN ReorderPoint
        ELSE @ReorderPoint
    END
 

/*
Вариант 3
выигрывает!
*/
SELECT *
FROM Production.Product 
WHERE @ReorderPoint IS NULL OR ReorderPoint > @ReorderPoint

Как видно из комментариев, трудно будет заставить некоторые операторы сравнения работать с CASE и ISNULL. Но возможность использования OR может помочь во многих случаях.

Заключение

Я не проводил сравнений производительности разных способов. Каждый из приведенных выше приемов хорош в своем месте. Попробуйте использовать разные способы и сравнить их производительность для конкретного запроса.

Буквально сразу после завершения этой короткой статьи я нашел две замечательных статьи SQL Server MVP Erland Sommarskog. Рекомендую их прочитать, если вы планируете работать с настраиваемыми выражениями WHERE в ваших приложениях.

Главное, что я понял из этих статей – sp_executesql выигрывает в большинстве случаев.

Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав.

Технология Клиент-Сервер 2008'3

TSQL LAB 2 – создание настраиваемых выражений WHERE

Автор: Джейкоб Себастиан (Jacob Sebastian)

Опубликовано: 28.04.2009

Введение

Создание и исполнение динамического запроса

SQL Injection

Использование sp_executesql

Применение условия WHERE без использования Dynamic Query

Заключение