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

Синтаксис

Синтаксис для sp_executesql выглядит следующим образом:

sp_executesql @stmt, N'@parametername1_datatype,@parametername2_datatype,@parameternameN_datatype', @parametername1 = 'Value1', @parametername2 = 'Value2', @parameternameN = 'ValueN'

Параметр @stmt указывает динамически созданный SQL-запрос или пакет. Тип данных этого параметра должен быть Unicode-строками, поэтому нам нужно добавить префикс N или использовать переменные с типом данных nvarchar или nchar. Параметр @parameternameN_datatype определяет имя параметра и тип данных, используемый в динамически созданных SQL-запросах. Мы можем присваивать значения этим параметрам с помощью выражения @parameternameN=’ValueN’.

Пример

Рассмотрим пример, в котором мы извлекаем данные из таблицы Person в базе данных AdventureWorks. Мы хотим отобразить определенные столбцы и отфильтровать данные на основе столбца PersonType.

DECLARE @SqlStatement AS NVARCHAR(1000)
DECLARE @ColNames AS NVARCHAR(100)
SET @ColNames = N'FirstName, MiddleName, LastName';
SET @SqlStatement = 'SELECT ' + @ColNames + ' FROM Person.Person WHERE PersonType=@PerType'
EXECUTE sp_executesql @SqlStatement, N'@PerType nchar(2)', @PerType = 'EM'

В этом примере мы присваиваем динамически созданный SQL-запрос переменной @SqlStatement. Переменная @ColNames указывает имена столбцов, которые будут отображаться в наборе результатов. Мы фильтруем данные на основе параметра @PerType, который имеет тип данных nchar(2) и фильтрует данные, где столбец PersonType равен “EM”. Затем выполняется запрос, и в набор результатов будут включены только указанные столбцы.

Использование параметра OUTPUT

sp_executesql также позволяет нам возвращать результат выполнения динамически созданного SQL-запроса или пакета с помощью параметра OUTPUT. Рассмотрим пример, в котором мы подсчитываем количество строк в таблице PersonPhone и присваиваем результат переменной:

DECLARE @SqlStatement AS NVARCHAR(1000)
DECLARE @PhoneIdType AS INT
DECLARE @Result AS INT
SET @SqlStatement = 'SELECT @RowNumber=COUNT(PhoneNumber) FROM Person.PersonPhone WHERE PhoneNumberTypeID=@PhoneType'
SET @PhoneIdType = 1
EXEC sp_executesql @SqlStatement, N'@PhoneType INT, @RowNumber INT OUTPUT', @PhoneType = @PhoneIdType, @RowNumber = @Result OUTPUT
SELECT @Result AS [TableRowNumber]

В этом примере мы указываем параметр @RowNumber как параметр OUTPUT и присваиваем его значение параметру @Result. Это позволяет нам получить количество строк из динамически созданного запроса.

sp_executesql против оператора EXEC

Оператор EXEC – это еще один вариант выполнения динамических SQL-запросов. Однако sp_executesql имеет некоторые преимущества перед оператором EXEC:

• sp_executesql имеет возможность повторного использования кэшированных планов запросов, что помогает снизить затраты на компиляцию запросов.
• sp_executesql позволяет генерировать параметризованные динамические запросы, что делает его более безопасным от атак SQL-инъекций.

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

Заключение

В этой статье мы рассмотрели процедуру sp_executesql в SQL Server. Мы узнали о ее синтаксисе, использовании и преимуществах по сравнению с оператором EXEC. sp_executesql – это мощный инструмент для выполнения динамически созданных SQL-запросов или пакетов, и он предлагает такие преимущества, как повторное использование планов запросов и защита от атак SQL-инъекций. Понимание и эффективное использование sp_executesql могут значительно улучшить возможности SQL-программирования.