SQL Server Динамический SQL

В этом руководстве вы узнаете, как использовать динамический SQL SQL Server для создания универсальных и гибких SQL-операторов.

Введение в динамический SQL

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

Создать динамический SQL просто, вам просто нужно сделать его строкой следующим образом:

'SELECT * FROM production.products';

Для выполнения динамического оператора SQL необходимо вызвать хранимую процедуру sp_executesql, как показано в следующем операторе:

EXEC sp_executesql N'SELECT * FROM production.products';

Поскольку sp_executesql принимает динамический SQL как строку Unicode, вам необходимо добавить к ней префикс N.

Хотя этот динамический SQL не очень полезен, он очень хорошо иллюстрирует динамический SQL.

Пример использования динамического SQL для запроса из любой таблицы

Сначала объявите две переменные: @table для хранения имени таблицы, из которой вы хотите сделать запрос, и @sql для хранения динамического SQL.

DECLARE 
    @table NVARCHAR(128),
    @sql NVARCHAR(MAX);

Во-вторых, установите значение переменной @table на production.products.

SET @table = N'production.products';

В-третьих, создайте динамический SQL, объединив оператор SELECT с параметром имени таблицы:

SET @sql = N'SELECT * FROM ' + @table;

В-четвертых, вызовите хранимую процедуру sp_executesql, передав параметр @sql.

EXEC sp_executesql @sql;

Собираем все вместе:

DECLARE 
    @table NVARCHAR(128),
    @sql NVARCHAR(MAX);
SET @table = N'production.products';
SET @sql = N'SELECT * FROM ' + @table;
EXEC sp_executesql @sql;

Приведенный выше блок кода создает точный набор результатов в виде следующего оператора:

SELECT * FROM production.products;

Чтобы запросить данные из другой таблицы, вы меняете значение переменной @table. Однако практичнее будет, если мы обернем приведенный выше блок T-SQL в хранимую процедуру.

Динамический SQL SQL Server и хранимые процедуры

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

CREATE PROC usp_query(
    @table NVARCHAR(128)
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    -- construct SQL
    SET @sql = N'SELECT * FROM ' + @table;
    -- execute the SQL
    EXEC sp_executesql @sql;
END;

Следующий оператор вызывает хранимую процедуру usp_query для возврата всех строк из таблицы production.brands:

EXEC usp_query 'production.brands';

Эта хранимая процедура возвращает 10 верхних строк из таблицы по значениям указанного столбца:

CREATE OR ALTER PROC usp_query_topn(
    @table NVARCHAR(128),
    @topN INT,
    @byColumn NVARCHAR(128)
)
AS
BEGIN
    DECLARE 
        @sql NVARCHAR(MAX),
        @topNStr NVARCHAR(MAX);
    SET @topNStr  = CAST(@topN as nvarchar(max));
    -- construct SQL
    SET @sql = N'SELECT TOP ' +  @topNStr  + 
                ' * FROM ' + @table + 
                    ' ORDER BY ' + @byColumn + ' DESC';
    -- execute the SQL
    EXEC sp_executesql @sql;
END;

Например, вы можете получить 10 самых дорогих продуктов из таблицы production.products:

EXEC usp_query_topn 
        'production.products',
        10, 
        'list_price';

Этот оператор возвращает 10 лучших товаров с наибольшим количеством на складе:

EXEC usp_query_topn 
        'production.tocks',
        10, 
        'quantity';

Динамический SQL-код SQL-сервера и SQL-инъекции

Давайте создадим новую таблицу с именем sales.tests для демонстрации:

CREATE TABLE sales.tests(id INT); 

Этот оператор возвращает все строки из таблицы production.brands:

EXEC usp_query 'production.brands';

Но это не мешает пользователям передавать имя таблицы следующим образом:

EXEC usp_query 'production.brands;DROP TABLE sales.tests';

Этот метод называется SQL-инъекцией. После выполнения оператора таблица sales.tests удаляется, поскольку хранимая процедура usp_query выполняет оба оператора:

SELECT * FROM production.brands;DROP TABLE sales.tests

Чтобы предотвратить эту SQL-инъекцию, вы можете использовать функцию QUOTENAME(), как показано в следующем запросе:

CREATE OR ALTER PROC usp_query
(
    @schema NVARCHAR(128), 
    @table  NVARCHAR(128)
)
AS
    BEGIN
        DECLARE 
            @sql NVARCHAR(MAX);
        -- construct SQL
        SET @sql = N'SELECT * FROM ' 
            + QUOTENAME(@schema) 
            + '.' 
            + QUOTENAME(@table);
        -- execute the SQL
        EXEC sp_executesql @sql;
    END;

Теперь, если передать схему и имя таблицы в хранимую процедуру, это будет работать:

EXEC usp_query 'production','brands';

Однако если вы попытаетесь ввести другое утверждение, например:

EXEC usp_query 
        'production',
        'brands;DROP TABLE sales.tests';

Будет выдана следующая ошибка:

Invalid object name 'production.brands;DROP TABLE sales.tests'.

Подробнее о хранимой процедуре sp_executesql

Процедура sp_executesql имеет следующий синтаксис:

EXEC sp_executesql 
    sql_statement  
    parameter_definition
    @param1 = value1,
    @param2 = value2,
    ...

В этом синтаксисе:

• sql_statement — это строка Unicode, которая содержит оператор T-SQL. SQL_statement может содержать такие параметры, как SELECT * FROM table_name WHERE id=@id
• Parameter_definition — это строка, содержащая определение всех параметров, встроенных в sql_statement. Каждое определение параметра состоит из имени параметра и его типа данных, например, @id INT. Определения параметров разделяются запятой(,).
• @param1 = value1, @param2 = value2,… укажите значение для каждого параметра, определенного в строке parameter_definition.

В этом примере используется хранимая процедура sp_executesql для поиска продуктов, цена по прейскуранту которых превышает 100 и которые относятся к категории 1:

EXEC sp_executesql
N'SELECT *
    FROM 
        production.products 
    WHERE 
        list_price> @listPrice AND
        category_id = @categoryId
    ORDER BY
        list_price DESC', 
N'@listPrice DECIMAL(10,2),
@categoryId INT'
,@listPrice = 100
,@categoryId = 1;

В этом руководстве вы узнали, как использовать динамический SQL SQL Server для создания универсальных и гибких SQL-операторов.