Динамический sql ms sql: Выполнение динамических T-SQL инструкций в Microsoft SQL Server | Info-Comp.ru

Содержание

Чем может быть полезен динамический SQL

Автор: Анатолий Котелевец

В наших проектах нам приходится решать различные задачи. Для решения некоторых из них мы используем dynamic T-Sql (далее по тексту dynamic sql).

Для чего нужен dynamic sql? Каждый решает для себя. В одном из проектов с помощью dynamic sql мы решили задачи построения динамичных отчетов, в других — миграцию данных. Также dynamic sql незаменим в случаях, когда требуется создать/изменить/получить данные или объекты, но значения/названия приходят в качестве параметров. Да, это может показаться абсурдом, но есть и такие задачи.
Дальше мы покажем несколько примеров, как это можно реализовать с помощью dynamic sql.

Выполнить динамическую команду можно несколькими способами:

С использование ключевого слова EXEC/EXECUTE;
C использование хранимой процедуры sp_executesql

Данные способы отличаются между собой кардинально. На небольшом примере мы постараемся пояснить, чем они отличаются.

Пример кода с EXEC/EXECUTE:

DECLARE @sql varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = ‘CustomerID, ContactName, City’
SET @city = ‘London’
SELECT @sql = ‘ SELECT CustomerID, ContactName, City ‘ +
‘ FROM dbo.customers WHERE 1 = 1 ‘
SELECT @sql = @sql + ‘ AND City LIKE »’ + @city + »»
EXEC (@sql)

DECLARE @sql varchar(1000)

DECLARE @columnList varchar(75)

DECLARE @city varchar(75)

SET @columnList = ‘CustomerID, ContactName, City’

SET @city = ‘London’

SELECT @sql = ‘ SELECT CustomerID, ContactName, City ‘ +

‘ FROM dbo.customers WHERE 1 = 1 ‘

SELECT @sql = @sql + ‘ AND City LIKE »’ + @city + »»

EXEC (@sql)

Как видно из запроса выше, мы формируем динамическую команду. Если выполнить select @sql, то результат будет следующий:

SELECT CustomerID, ContactName, City FROM customers WHERE City = ’London'

Что же тут плохого? — Запрос отработает, и все будут довольны. Но все же, есть несколько причин, почему так делать не стоит:

При написании команды очень легко ошибиться с количеством «’», т.к. необходимо указывать дополнительные «’», чтобы передать текстовое значение в запрос.
При таком запросе возможны Sql инъекции (SQL Injection). Например, стоит задать значение для @city вроде такого
set @city = »’DROP TABLE customers—»'

set @city = »’DROP TABLE customers—»'

— и результат будет печальный, т. к. операция select выполнится успешно, как и операция DROP TABLE customers.
Возможна ситуация, когда у вас будет несколько переменных, содержащих коды ваших команд. Что-то типа такой EXEC(@sql1 + @sql2 + @sql3).
Какие трудности могут возникнуть тут?
Нужно помнить, что каждая команда отработает отдельно, хотя на первый взгляд, может показаться, что будет выполнена операция конкатенации (@sql1 + @sql2 + @sql3), а затем выполнится общая команда. Также нужно помнить, что накладывается общее ограничение на параметр команды EXEC в 4000 символов.
Происходит неявное приведение типов, т.к. параметры передаются в виде строки.

Что изменится при использовании sp_executesql? – Разработчику проще писать код и его отлаживать, т.к. код будет написан практически как обычный Sql запрос.

Пример кода с sp_executesql:

DECLARE @sqlCommand varchar (1000)
DECLARE @columnList varchar (75)
DECLARE @city varchar (75)
SET @city = ‘London’
SET @sqlCommand = ‘SELECT CustomerID, ContactName, City FROM customers WHERE City = @city’
EXECUTE sp_executesql @sqlCommand, N’@city nvarchar(75)’, @city = @city

DECLARE @sqlCommand varchar (1000)

DECLARE @columnList varchar (75)

DECLARE @city varchar (75)

SET @city = ‘London’

SET @sqlCommand = ‘SELECT CustomerID, ContactName, City FROM customers WHERE City = @city’

EXECUTE sp_executesql @sqlCommand, N’@city nvarchar(75)’, @city = @city

Что же изменилось?

В отличие от EXECUTE при использовании sp_executesql, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.
Это решает проблему с дополнительными «’».
Решается проблема безопасности — Sql инъекции (SQL Injection).

Для обоих подходов планы запросов кэшируются, но они отличаются. Эти отличия приведены на рисунке 1 и рисунке 2.

Получение плана запроса:

SELECT q.TEXT,cp.usecounts,cp.objtype,p.*, q.*, cp.plan_handle
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE
q.TEXT NOT LIKE ‘%sys.dm_exec_cached_plans %’
and cp.cacheobjtype = ‘Compiled Plan’
AND q.TEXT LIKE ‘%customers%’

SELECT q.TEXT,cp.usecounts,cp.objtype,p.*, q.*, cp.plan_handle

FROM

sys.dm_exec_cached_plans cp

CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p

CROSS apply sys. dm_exec_sql_text(cp.plan_handle) AS q

WHERE

q.TEXT NOT LIKE ‘%sys.dm_exec_cached_plans %’

and cp.cacheobjtype = ‘Compiled Plan’

AND q.TEXT LIKE ‘%customers%’

План запроса при использовании EXEC:

План запроса при использовании sp_executesql:

Также одно из преимуществ использования sp_executesql – это возможность возвращать значение через OUT параметр.

Далее приведем пример, как мы решили одну из проблем в проекте с использованием dynamic sql.

Допустим, у нас есть товар (да неважно, собственно, что это: товар, анкета на должность, персональная анкета). Смысл в том, что каждый объект имеет свой набор свойств (атрибутов), который его характеризует, а их может быть разное количество, и они будут разного типа. Как хранить в БД – это проблема архитектуры.

Для клиента нужен был отчет, который из себя представлял n строк на m столбцов. Где m и был наш набор атрибутов. Отчет собирался по группе объектов или для какого-то объекта из группы. Но смысл остается все тот же: каждый отчет содержит разное количество столбцов для каждой группы объектов.

Поскольку изначально существовала связь между объектами, то решение проблемы выбрали без изменения архитектуры БД. На наш взгляд, решений данной проблемы может быть несколько:

Использовать систему отчетности, например, MS Sql Reporting Service. Создать матричный отчет, а в качестве запроса у нас будет «простой» Select. Почему мы так не сделали? В проекте не так много было отчетов, чтобы внедрять туда SSRS.
Использовать тот же «простой» select и на серверной стороне уже создавать DataSet необходимой «формы». Да, так задача была решена изначально, когда данных о товарах было очень мало. Как только данных стало достаточно много, то время сбора отчета стало выходит за установленный timeout.
Использовать Pivot в sql. Да, отличное решение, когда вы знаете, что у вас только эти атрибуты, и новых не будет. А что делать, когда количество атрибутов часто меняется. И опять же, для каждой группы объектов у нас свой набор атрибутов, мы снова вернемся к созданию процедуры для каждой группы объектов. Не очень удобное решение, не правда ли?
А если использовать Pivot, но добавить туда немного dynamic sql? – Да, это решение, которое имеет право на жизнь. Его мы и опишем, как пример использования dynamic sql…

Ссылка на скрипты для создания таблиц и запроса.

В основе отчета будет лежать обычный запрос:

Основной код для отчёта:

SELECT p.Id as ProductID,

p.Name as [Наименование],

pcp.Name as PropertiesName,

vpp.Value as Value

FROM dbo.Products p

INNER JOIN dbo.PropertiesCategoryOfProducts pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId

INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id

and vpp.PropertiesCategoryOfProductsId = pcp.Id

where p.CategoryOfProductsId = @CategoryOfProductsId

Код запроса для построения отчёта:

SELECT p.Id as ProductID,
p.Name as [Наименование],
pcp.Name as PropertiesName,
vpp.Value as Value
FROM dbo.Products p
INNER JOIN dbo.PropertiesCategoryOfProducts pcp ON pcp.CategoryOfProductsId = p. CategoryOfProductsId
INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id
and vpp.PropertiesCategoryOfProductsId = pcp.Id
where p.CategoryOfProductsId = @CategoryOfProductsId

Код запроса для построения отчета
declare @CategoryOfProductsId int = 1

declare @PivotColumnHeaders nvarchar(max)=
REVERSE(STUFF(REVERSE((select ‘[‘ + Name + ‘]’ + ‘,’ as ‘data()’
from dbo.PropertiesCategoryOfProducts t
where t.CategoryOfProductsId = @CategoryOfProductsId
FOR XML PATH(»)
)),1,1,»))

if(@PivotColumnHeaders>»)
declare @PivotTableSQL nvarchar(max)
BEGIN
SET @PivotTableSQL = N’
SELECT *
from (SELECT p.Id as ProductID,
p.Name as [Наименование],
pcp.Name as PropertiesName,
vpp.Value as Value
FROM dbo.Products p
INNER JOIN dbo.PropertiesCategoryOfProducts pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId
INNER JOIN dbo.ValueOfProductProperty vpp ON vpp. ProductID = p.Id
and vpp.PropertiesCategoryOfProductsId = pcp.Id
where p.CategoryOfProductsId = @CategoryOfProductsId
) as Pivot_Data
PIVOT (
MIN(Value)
FOR PropertiesName IN (
‘ + @PivotColumnHeaders + ‘
)
) AS PivotTable
‘

EXECUTE sp_executesql @PivotTableSQL, N’@CategoryOfProductsId int’, @CategoryOfProductsId = @CategoryOfProductsId;
END

SELECT p.Id as ProductID,

p.Name as [Наименование],

pcp.Name as PropertiesName,

vpp. Value as Value

FROM dbo.Products p

INNER JOIN dbo.PropertiesCategoryOfProducts pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId

INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id

and vpp.PropertiesCategoryOfProductsId = pcp.Id

where p.CategoryOfProductsId = @CategoryOfProductsId

Код запроса для построения отчета

declare @CategoryOfProductsId int = 1

declare @PivotColumnHeaders nvarchar(max)=

REVERSE(STUFF(REVERSE((select ‘[‘ + Name + ‘]’ + ‘,’ as ‘data()’

from dbo.PropertiesCategoryOfProducts t

where t.CategoryOfProductsId = @CategoryOfProductsId

FOR XML PATH(»)

)),1,1,»))

if(@PivotColumnHeaders>»)

declare @PivotTableSQL nvarchar(max)

BEGIN

SET @PivotTableSQL = N’

SELECT *

from (SELECT p.Id as ProductID,

p.Name as [Наименование],

pcp.Name as PropertiesName,

vpp.Value as Value

FROM dbo.Products p

INNER JOIN dbo. PropertiesCategoryOfProducts pcp ON pcp.CategoryOfProductsId = p.CategoryOfProductsId

INNER JOIN dbo.ValueOfProductProperty vpp ON vpp.ProductID = p.Id

and vpp.PropertiesCategoryOfProductsId = pcp.Id

where p.CategoryOfProductsId = @CategoryOfProductsId

) as Pivot_Data

PIVOT (

MIN(Value)

FOR PropertiesName IN (

‘ + @PivotColumnHeaders + ‘

)

) AS PivotTable

‘

EXECUTE sp_executesql @PivotTableSQL, N’@CategoryOfProductsId int’, @CategoryOfProductsId = @CategoryOfProductsId;

END

Давайте рассмотрим, что же мы тут написали:

Инициализируем переменную со значением нашей категории товаров — declare @CategoryOfProductsId int = 1

Далее нам нужно получить список колонок для нашей категории товаров, но при этом они должны быть заключены в “[]” скобки и перечислены через “,” как этого требует синтаксис функции Pivot —

Получение списка колонок для категории товаров:

declare @PivotColumnHeaders nvarchar(max)=
REVERSE(STUFF(REVERSE((select ‘[‘ + Name + ‘]’ + ‘,’ as ‘data()’
from dbo. PropertiesCategoryOfProducts t
where t.CategoryOfProductsId = @CategoryOfProductsId
FOR XML PATH(»)
)),1,1,»))

declare @PivotColumnHeaders nvarchar(max)=

REVERSE(STUFF(REVERSE((select ‘[‘ + Name + ‘]’ + ‘,’ as ‘data()’

from dbo.PropertiesCategoryOfProducts t

where t.CategoryOfProductsId = @CategoryOfProductsId

FOR XML PATH(»)

)),1,1,»))

Ну а дальше все просто: при выполнении кода список колонок для функции Pivot будет подставлен из @PivotColumnHeaders

Если выполнить select @PivotTableSQL, то мы получим тот запрос, который без использования dynamic sql нам бы пришлось писать вручную.

Результатом выполнения данного запроса будет отчет такого вида:

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

Запись опубликована в рубрике Полезно и интересно с метками dynamic, tsql. Добавьте в закладки постоянную ссылку.

20761 Запрос данных с использованием Transact SQL в Минске

Количество часов

40 ак. ч

Продолжительность

5 занятий

Стоимость курса

920 BYN

Цель курса – предоставить слушателям знания и навыки, необходимые для написания базовых запросов на Transact-SQL для Microsoft SQL Server 2016. Курс предоставляет необходимый минимум знаний для понимания работы Microsoft SQL Server, и является базовым курсом для администраторов и разработчиков баз данных, а также специалистов в области бизнес-аналитики.

Режим и формат обучения:

Расписание: ПН-ПТ
Время: 09:00-16:00
Формат: Гибридный (на выбор) — онлайн в режиме реального времени или офлайн по адресу: г. Минск, ул. Амураторская, 4. (ст. м. Молодежная).

Этот курс предназначен для администраторов БД, разработчиков БД и клиентских приложений, и специалистов в области бизнес-аналитики. Курс будет интересен для пользователей, желающих понимать работу БД или осмысленно формировать отчеты, а также слушателей, планирующих сдать экзамен.

По окончании курса вы сможете:

Описать ключевые возможности и компоненты SQL Server 2016.

Описать возможности T-SQL, наборы и логику предиката.

Писать SELECT запросы.

Выполнять запросы из нескольких таблиц.

Сортировать и фильтровать данные.

Описать типы данных в SQL Server.

Изменять данные с помощью Transact-SQL.

Использовать встроенные функции.

Группировать и агрегировать данные.

Использовать подзапросы.

Создавать представления и использовать функции, возвращающие табличные значения.

Использовать оператор SET для объединения результатов запросов.

Писать запросы с ранжированием, смещением и функциями агрегирования.

Преобразовывать данные с помощью функций pivot, unpivot, rollup и cube.

Создавать и внедрять хранимые процедуры.

Использовать переменные, условия и циклы в запросах Transact-SQL.

По окончании курса вы получите:

Сертификат Microsoft о прохождении курсов.

Свидетельство государственного образца о повышении квалификации (при наличии высшего образования или средне-специального образования).

Необходимая подготовка:

Для эффективного обучения на курсе, слушатели должны обладать следующими знаниями и навыками:

Базовые знания о реляционных базах данных.

Базовые знания об основной функциональности ОС Windows.

Преподаватель курса:

Михаил Пилинога – Microsoft Certified Trainer, более 20 лет практического опыта.

Навыки и опыт:

Доскональное знание систем на основе Windows–технологий, включая как серверные операционные системы Windows NT 4.0, Windows 2000, 2003, 2008, 2012, 2016, так и клиентские — Windows 2000, Windows XP, Windows 7, Windows 8, Windows 10

Глубокое знание серверов Microsoft Exchange Server (5.5, 2000, 2003, 2007, 2010, 2013, 2016)

Знание и опыт работы с Microsoft SQL Server (2008, 2014, 2016)

Опыт внедрения решения инфраструктуры в Microsoft Azure, мониторинга облачных и гибридных решений и поддержки управления жизненным циклом приложений

Настройка, конфигурирование, администрирование и эксплуатация локальных сетей, коммутаторов 3Com, маршрутизаторов Cisco, точек беспроводного доступа D-Link

Опыт работы со службой каталогов Active Directory

Знание аппаратной части PC, опыт подготовки технических спецификаций серверного, компьютерного, сетевого и периферийного оборудования; практический опыт работы с серверами IBM и Fujitsu-Siemens

Опыт руководства группой администраторов, некоторый опыт работы в подразделении безопасности (информационная защита)

Наличие опыта работы с программой шифрования PGP и системами распределения открытых ключей PKI (на базе Windows Server 2003 и Windows Server 2008), знание теории криптографии

Некоторый опыт программирования на C++, C#, ассемблере для х86 процессоров, ASP. NET, PowerShell

Программа курса:

Модуль 1: Введение в Microsoft SQL Server 2016

В этом модуле представлено введение в SQL Server, описаны версии SQL Server, включая облачные версии и способы подключения к SQL Server с использованием SQL Server Management Studio.

Базовая архитектура SQL Server

SQL Server Editions и версии

Начало работы с SQL Server Management Studio

Лабораторная работа: Работа с инструментами SQL Server 2016

Работа с SQL Server Management Studio

Написание T-SQL сценариев

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

Модуль 2: Введение в язык запросов T-SQL

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

Введение в T-SQL

Понятие набора данных

Понятие логики предиката

Понятие логики последовательности операторов в SELECT выражениях

Лабораторная работа: Введение в язык запросов T-SQL

Выполнение базовых SELECT выражений

Выполнение запросов с фильтрацией данных, используя предикаты

Выполнение запросов с сортировкой данных, используя ORDER BY

Модуль 3: Написание запросов SELECT

В этом модуле представлены основы инструкции SELECT для запросов по одной таблице.

Запись простых инструкций SELECT

Устранение дубликатов с DISTINCT

Использование псевдонимов столбцов и таблиц

Написание простых выражений CASE

Лабораторная работа: Запись основных операторов SELECT

Запись простых инструкций SELECT

Удаление дубликатов с использованием DISTINCT

Использование псевдонимов столбцов и таблиц

Использование простого выражения CASE

Модуль 4: Запрос данных из нескольких таблиц

Этот модуль описывает, как писать запросы для объединения данных из нескольких источников в Microsoft SQL Server 2016.

Понятие объединений

Написание запросов с использованием внутреннего объединения INNER JOIN

Написание запросов с использованием внешнего объединения OUTER JOIN

Написание запросов с использованием перекрестного объединения CROSS JOIN, объединения таблицы с собой (SELF JOIN)

Лабораторная работа: Запрос данных из нескольких таблиц

Написание запросов с использованием INNER JOIN

Написание запросов с использованием многотабличных объединений

Написание запросов с использованием объединения таблицы сама с собой

Написание запросов с использованием OUTER JOIN

Написание запросов с использованием перекрестного объединения CROSS JOIN

Модуль 5: Сортировка и фильтрация данных

Этот модуль описывает, как выполнять сортировку и фильтрацию.

Сортировка данных

Фильтрация данных

Фильтрация с использованием опций TOP и OFFSET-FETCH

Работа с неизвестными значениями

Лабораторная работа: Работа с подзапросами

Написание запросов с фильтрацией данных с использованием предложения WHERE

Написание запросов с сортировкой данных с использованием предложения ORDER BY

Написание запросов с фильтрацией данных с использованием предложения TOP

Модуль 6: Работа с типами данных SQL Server 2016

В этом модуль описаны типы данных, используемые SQL Server для хранения данных.

Введение в типы данных SQL Server 2016

Работа с текстовыми типами данных

Работа с типами данных Дата и Время

Лабораторная работа: Работа с типами данных SQL Server 2016

Написание запросов, возвращающих типы данных Дата и Время

Написание запросов, использующих функции даты и времени

Написание запросов, возвращающие текстовые данные

Написание запросов, использующих текстовые функции

Модуль 7: Использование DML для изменения данных

В этом модуле описывается, как и зачем создавать запросы DML.

Добавление данных

Изменение и удаление данных

Лабораторная работа: Использование DML для изменения данных

Добавление данных

Обновление и удаление данных

Модуль 8: Использование встроенных функций

В этом модуле представлены некоторые из встроенных функций SQL Server 2016.

Написание запросов, использующих встроенные функции

Использование функций преобразования

Использование логических функций

Использование функций на проверку NULL

Лабораторная работа: Использование встроенных функций

Написание запросов с функциями преобразования

Написание запросов с логическими функциями

Написание запросов с функцией на проверку NULL

Модуль 9: Группировка и агрегирование данных

Этот модуль описывает, как использовать функции агрегирования.

Использование функций агрегирования

Использование предложения GROUP BY

Фильтрация сгруппированных данных с использованием предложения HAVING

Лабораторная работа: Группировка и агрегирование данных

Написание запросов с использованием предложения GROUP BY

Написание запросов с использованием функций агрегирования

Написание запросов с использованием отдельных статистических функций

Написание запросов, фильтрующих сгруппированные данные с использованием предложения HAVING

Модуль 10: Использование вложенных запросов

Этот модуль описывает несколько типов вложенных запросов и способы их использования.

Написание автономных вложенных подзапросов

Написание корреляционных подзапросов

Использование предиката EXISTS в подзапросах

Лабораторная работа: Использование подзапросов

Написание автономных вложенных подзапросов

Написание запросов с использованием скалярных и табличных подзапросов

Использование корреляционных подзапросов и EXISTS в подзапросах

Модуль 11: Использование табличных выражений

Ранее в курсе было описано использование подзапросов в качестве выражения, которое возвращало результаты внешнему вызывающему запросу. Как и подзапросы, табличные выражения представляют собой выражения запроса, но выражения в таблицах расширяют эту идею, позволяя именовать их и работать с их результатами в любой допустимой реляционной таблице. Microsoft SQL Server 2016 поддерживает четыре типа табличных выражений: производные таблицы, общее табличное выражение (CTE), представления и встроенные табличные функции (TVF). В этом модуле показано, как работать с этими формами табличных выражений, и описано, как использовать их для создания модульного подхода к написанию запросов.

Использование производных таблиц

Использование общих табличных выражений

Использование представлений

Использование встроенных табличных выражений

Лабораторная работа: Использование табличных выражений

Написания запросов, использующих представления

Написания запросов, использующих производные таблицы

Написания запросов, использующих общие табличные выражения

Написания запросов, использующих встроенные табличные выражения

Модуль 12: Использование операторов наборов строк

В этом модуле описывается использование операторов UNION, INTERSECT и EXCEPT для сравнения строк между двумя наборами ввода.

Написание запросов, использующих оператор UNION

Написание запросов, использующих операторы EXCEPT и INTERSECT

Написание запросов, использующих оператор APPLY

Лабораторная работа: Использование операторов наборов строк

Написание запросов, использующих операторы UNION и UNION ALL

Написание запросов, использующих операторы CROSS APPLY и OUTER APPLY

Написание запросов, использующих операторы EXCEPT и INTERSECTS

Модуль 13: Использование функций ранжирования, смещения и статистических функций

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

Создание окон с помощью предложения OVER

Обзор функций окна

Лабораторная работа: Использование функций ранжирования, смещения и статистических функций

Написание запросов с использованием функций ранжирования

Написание запросов с использованием функций смещения

Написание запросов с использованием функций агрегирования

Модуль 14: Создание сводных результирующих наборов и группировка

Этот модуль описывает запросы PIVOT и UNPIVOT, написание запросов, которые задают несколько группировок с наборами групп

Написание запросов с использованием операторов PIVOT и UNPIVOT

Группировка данных

Лабораторная работа: Создание сводных результирующих наборов и группировка

Написание запросов с использованием оператора PIVOT

Написание запросов с использованием оператора UNPIVOT

Написание запросов с использованием GROUPING SETS CUBE и ROLLUP

Модуль 15: Запуск хранимых процедур

В этом модуле описывается, как возвращать результаты, выполняя хранимые процедуры, передавать параметры процедурам, создавать простые хранимые процедуры, которые инкапсулируют инструкцию SELECT, создавать и выполнять динамический SQL код с EXEC и sp_executesql.

Запрос данных хранимой процедурой

Передача параметров хранимым процедурам

Создание простой хранимой процедуры

Работа с динамическим SQL

Лабораторная работа: Запуск хранимых процедур

Использование оператора EXECUTE для запуска хранимых процедур

Передача параметров хранимым процедурам

Запуск системных хранимых процедур

Модуль 16: Программирование с использованием возможностей T-SQL

Этот модуль описывает, как улучшить код T-SQL с помощью элементов программирования.

Элементы программирования T-SQL

Управление программными потоками

Лабораторная работа: Программирование с использованием возможностей T-SQL

Объявление переменных и снятие ограничений с пакетов

Использование элементов управления потоком выполнения

Использование динамического SQL

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

Модуль 17: Реализация управления ошибками

В этом модуле представлена обработка ошибок для T-SQL.

Внедрение обработки ошибок T-SQL

Внедрение структурированной обработки исключений

Лабораторная работа: Реализация управления ошибками

Перенаправление ошибок с использованием TRY/CATCH

Использование THROW для передачи сообщений об ошибках обратно клиенту

Модуль 18: Реализация транзакций

Этот модуль описывает, как реализовать транзакции.

Транзакции и базы данных

Управление транзакциями

Лабораторная работа: Реализация транзакций

Определение транзакций с использованием блоков BEGIN, COMMIT и ROLLBACK

Добавление логики обработки ошибок в блок CATCH

Получить дополнительную информацию:
+375 29 690 40 33, [email protected]

Совет от Института

20762 Разработка баз данных SQL

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

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

Динамический SQL — это оператор SQL, который создается и выполняется во время выполнения на основе переданных входных параметров. Позволять
Рассмотрим несколько примеров с использованием команды EXEC и расширенной хранимой процедуры sp_executesql.

Выполнение динамического SQL с помощью команды EXEC/EXECUTE

Команда EXEC выполняет хранимую процедуру или переданную ей строку. Дополнительные сведения см. в обзоре EXEC SQL и примерах .
и примеры по команде EXEC.

В следующем примере показано построение оператора SQL с использованием входной переменной и выполнение оператора SQL.
оператор с помощью команды EXEC.

— Простой динамический оператор SQL 003

SET @SQL = ‘SELECT ProductID, Name, ProductNumber FROM SalesLT.Product, где ProductID = ‘+ @Pid

EXEC (@SQL)

Существует возможность внедрения SQL-кода при построении оператора SQL путем объединения строк из пользовательских
входные значения. Я надеюсь рассказать о SQL-инъекциях и некоторых методах предотвращения SQL-инъекций в своих будущих статьях.

Мы должны позаботиться о нулевых значениях при объединении строк из параметров с помощью оператора «+». Ниже
Например, я закомментировал оператор, который устанавливает значение переменной « @pid ».

По умолчанию переменная « @pid » имеет значение NULL, поскольку мы не устанавливали никакого значения. Окончательное заявление
созданный после конкатенации, пуст, так как оператор «+» не обрабатывает нулевые значения. Пожалуйста, обратитесь к ниже
изображение, показывающее, что окончательное значение переменной «@SQL» пусто.

В этом случае используйте функцию ISNULL , чтобы создать правильный оператор SQL, в то время как
объединение строк с помощью оператора «+».

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

15 9000 3

DECLARE @SQL nvarchar(1000)

declare @Pid varchar(50)

set @pid = ‘689’

SET @SQL = ‘ВЫБЕРИТЕ ProductID, Name, ProductNumber FROM SalesLT.Product, где ProductID = ‘+ @Pid

EXEC (@SQL)

DECLARE @SQL nvarchar(1000)

9 0002 declare @Pid varchar(50)

set @pid = ‘681’

SET @SQL = ‘SELECT ProductID, Name, ProductNumber FROM SalesLT.Product, где ProductID = ‘+ @Pid

9000 2

EXEC (@SQL)

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

Выполнение динамического SQL с помощью sp_executesql

sp_executesql — это расширенная хранимая процедура, которую можно использовать для выполнения динамических операторов SQL в SQL Server. мы
необходимо передать оператор SQL и определение параметров, используемых в операторе SQL, и, наконец, установить значения
к параметрам, используемым в запросе.

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

sp_executesql @stmt, N’@parameter_name data_type’, @param1 = ‘value1’

В приведенном ниже примере показано выполнение динамического оператора SQL путем передачи параметров в расширенную хранимую процедуру sp_executesql.
процедура.

EXECUTE sp_executesql

N’SELECT ProductID, Name, ProductNumber

FROM SalesLT. Product, где ProductID = @Pid и ProductNumber=@PNumber’,

N’@Pid varchar(50),@PNumber varchar(50)’,

@pid = ‘680’,@PNumber=’FR-R92B-58′;

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

15 9000 3

EXECUTE sp_executesql

N’SELECT ProductID, Name, ProductNumber

FROM SalesLT.Product, где ProductID = @Pid and ProductNumber=@PNumber’,

N’@Pid varchar(50),@PNumber varchar(50)’,

@pid = ‘680’,@PNumber=’FR-R92B-58′;

EXECUTE sp_executesql

N’SELECT ProductID,Name,ProductNumber

FROM SalesLT. Product, где ProductID = @Pid and ProductNumber=@PNNumber’,

N’@Pid varchar(50),@PNumber varchar(50)’,

@pid = ‘681’,@PNumber=’FR-R92B-58′;

EXECUTE sp_executesql

N’SELECT ProductID,Name,ProductNumber

FROM SalesLT.Product,

N’@Pid varchar(50),@PNumber varchar(50)’,

@pid = ‘682’,@PNumber=’FR-R92B-58′;

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

Использование динамического SQL внутри хранимых процедур

Ниже приведен пример использования динамического SQL внутри хранимой процедуры. Для демонстрации я использовал таблицу продуктов.
из базы данных AdventureWorksLT. Эта хранимая процедура используется для поиска продуктов на основе разных столбцов.
как имя, цвет, productid и номер продукта. Оператор динамического SQL создается на основе входных данных.
передается хранимой процедуре и выполняется командой EXEC.

9 9 0003

17 9000 3

9 0002 26

90 002 39

СОЗДАТЬ ПРОЦЕДУРУ [dbo].[usp_SearchProducts]

(

@ProductID NVARCHAR(50) = NULL

,@Name NVARCHAR(100) = NULL

, @ProductNumber NVARCHAR(100) = NULL

,@Color NVARCHAR(100) = NULL

)

BEGIN

900 02 УСТАНОВИТЬ NOCOUNT ON;

DECLARE @SQL VARCHAR(MAX)

DECLARE @ProductIDFilter VARCHAR(MAX)

DECLARE @NameFilter VARCHAR(MAX)

DECLARE @ProductNumberFilter VARCHAR(MAX)

DECLARE @ColorFilter VARCHAR(MAX)

DECLARE @all VARCHAR(2) = ‘-1’

SET @ProductIDFilter = CASE WHEN @ProductID IS NULL OR @ProductID = 0

THEN »» + @all + »’ = »’ + @all + »’ ‘

ELSE ‘ProductID = »’ + @ProductID + »»

END

SET @NameFilter = CASE WHEN @Name IS NULL ИЛИ @Name = »

THEN »» + @all + »’ = »’ + @all + »»

ELSE ‘Имя как »%’ + @Name + ‘%»’

END

SET @ProductNumberF ilter = CASE WHEN @ProductNumber IS NULL ИЛИ @ProductNumber = »

THEN »» + @all + »’ = »’ + @all + »»

ELSE ‘ProductNumber как »%’ + @ProductNumber + ‘%»’

END

SET @ColorFilter = CASE WHEN @Color IS NULL OR @Color = »0003

ELSE ‘Цвет как »’ + @Color + »»

END

SET @SQL = ‘SELECT ProductID

,Name

900 02, ProductNumber

, Color

, StandardCost

, Size

, Weight

FROM SalesLT. Product

WHERE ‘ + @ProductIDFilter 9000 3

+ ‘ AND ‘ + @NameFilter + »

+ ‘ AND ‘ + @ProductNumberFilter + »

+ ‘ AND ‘ + @ColorFilter + »

PRINT (@sql)

EXEC(@sql)

КОНЕЦ

Когда мы выполняем хранимую процедуру только с входным параметром productid, оператор SQL строится, как показано ниже.
на изображении ниже.

Пожалуйста, обратитесь к изображению ниже, на котором показан другой оператор SQL, созданный, когда productid и product number
передаются в качестве входных параметров хранимой процедуре.

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

9 9 0003

24 90 003

90 002 41

СОЗДАТЬ ПРОЦЕДУРУ [dbo]. [usp_SearchProducts2]

(

@ProductID NVARCHAR(50) = NULL

,@Name NVARCHAR(100) = NULL

,@ProductNumber NVARCHAR(100) = NULL

,@Color NVARCHAR(100) = NULL

)

BEGIN

90 002 ВКЛЮЧИТЬ NOCOUNT;

DECLARE @SQL NVARCHAR(MAX)

DECLARE @ParameterDef NVARCHAR(500)

SET @ParameterDef = ‘@ProductID NVARCHAR(50),

@Name NV ARCHAR(100),

@ProductNumber NVARCHAR(100),

@Color NVARCHAR(100)’

SET @SQL = ‘SELECT ProductID

, Name

, ProductNumber

, Color

, StandardCost

, Size

,Вес

FROM SalesLT.Product WHERE -1=-1 ‘

ЕСЛИ @ProductID НЕ NULL И @ProductID <> 0

SET @SQL = @SQL+ ‘ AND ProductID = @ProductID’

IF @Name IS NOT NULL AND @Name <> »

SET @SQL = @SQL+ ‘ И Имя вида »%» + @Name + »%»’

9000 2

ЕСЛИ @ProductNumber НЕ NULL И @ProductNumber <>»

SET @SQL = @SQL+ ‘ AND ProductNumber как »%» + @ProductNumber + »%»’

ЕСЛИ @Color НЕ NULL И @Color <>»

SET @SQL = @SQL+ ‘ И Цвет как »%» + @Color + »%»’ @SQL, @ParameterDef, @ProductID=@ProductID,@Name=@Name,@ProductNumber=@ProductNumber,@Color=@Color

КОНЕЦ

ВПЕРЕД

Давайте выполним приведенный ниже пример потока, который извлечет все продукты, отмеченные красным цветом.

[usp_SearchProducts2] @ProductID=0,@Name=»,@ProductNumber=»,@Color = ‘красный’

Параметр OUTPUT в sp_executesql

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

Ниже приведен пример сценария, который показывает использование выходной переменной в sp_executesql.

9 9 0003

DECLARE @ProdNumber nvarchar(50)

EXECUTE sp_executesql N’

SELECT @ProdNumberOUT= ProductNumber

ОТ SalesLT .Product где ProductID = @Pid’

,N’@Pid varchar(50) ,@ProdNumberOUT nvarchar(25) OUTPUT’

,@pid = ‘680’

, @ProdNumberOUT = @ProdNumber ВЫВОД

выберите @ProdNumber в качестве ProductNumber

Временные таблицы в динамическом SQL

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

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

9 9 0003

CREATE TABLE #temptable (

ProductID VARCHAR(50)

,Name VARCHAR(100)

,ProductNumber VARCHAR(100)

)

ВЫПОЛНИТЬ sp_executesql N ‘ВСТАВИТЬ В #temptable

SELECT ProductID,Name,ProductNumber

FROM SalesLT.Product, где ProductID = @Pid and ProductNumber=@PNumber’

,N’@Pid varchar(50),@PNumber varchar(50)’

,@ pid = ‘680’

, @PNumber = ‘FR-R92B-58’;

ВЫБЕРИТЕ *

ИЗ #temptable

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

Сравнение команды EXEC и расширенной хранимой процедуры sp_executesql

sp_executesql	Команда EXEC
Повторно использует кешированный план	Генерирует несколько планов при выполнении с разными параметрами
Менее подвержен SQL-инъекциям	Склонен к SQL-инъекциям
Поддерживает параметризацию	Не поддерживает параметризацию
Поддерживает выходную переменную	Выходная переменная не поддерживается

Заключение

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

Автор
Последние сообщения

Ранга Бабу

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

Последние сообщения Ранги Бабу (посмотреть все)

Как выполнить динамический SQL в SQL Server

Автор Иосип Сабан• 09 июня 2021 г.•
09:56•
Разработка баз данных, операторы, хранимые процедуры

ГлавнаяРазработка баз данных, операторы, хранимые процедурыВыполнение динамического запроса в SQL Server

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

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

Мы используем два способа выполнения динамического SQL: команда EXEC и хранимая процедура sp_executesql .

Содержание

Как использовать команду EXEC или EXECUTE
Примеры использования расширенной процедуры sp_executesql
Динамический SQL в хранимых процедурах
Параметр OUTPUT в sp_executesql
Защита от SQL-инъекций с помощью процедуры sp_executesql
Сравнение функций команды EXEC и хранимой процедуры sp_executesql
Заключение

Как использовать команду EXEC или EXECUTE

В первом примере мы создаем простую динамическую инструкцию SQL из AdventureWorks база данных. В примере есть один фильтр, который передается через составную строковую переменную @AddressPart и выполняется в последней команде:

 USE AdventureWorks2019. 
-- Объявить переменную для хранения сгенерированного оператора SQL
ОБЪЯВИТЬ @SQLExec NVARCHAR(4000)
DECLARE @AddressPart NVARCHAR(50) = 'a'
-- Построить динамический SQL
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''
-- Выполнение динамического SQL
EXEC (@SQLExec)

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

Затем мы должны обрабатывать значения NULL в конкатенациях строк . Например, переменная экземпляра @AddressPart из предыдущего примера может сделать недействительным весь оператор SQL, если будет передано это значение.

Самый простой способ справиться с этой потенциальной проблемой — использовать функцию ISNULL для создания действительного оператора SQL.

Важно! Команда EXEC не предназначена для повторного использования кэшированных планов выполнения! Он будет создавать новый для каждого выполнения.

Чтобы продемонстрировать это, мы дважды выполним один и тот же запрос, но с другим значением входного параметра. Затем сравниваем планы выполнения в обоих случаях:

 USE AdventureWorks2019
-- Дело 1
ОБЪЯВИТЬ @SQLExec NVARCHAR(4000)
DECLARE @AddressPart NVARCHAR(50) = 'a'
 
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''
EXEC (@SQLExec)
-- Случай 2
УСТАНОВИТЕ @AddressPart = 'б'
 
SET @SQLExec = 'SELECT * FROM Person.Address WHERE AddressLine1 LIKE ''%' + @AddressPart + '%'''
EXEC (@SQLExec)
-- Сравните планы
ВЫБЕРИТЕ chdpln.objtype
, chdpln.cacheobjtype
, chdpln.usecounts
, sqltxt.текст
  ИЗ sys.dm_exec_cached_plans как chdpln
       ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ sys.dm_exec_sql_text(chdpln.plan_handle) как sqltxt
 ГДЕ sqltxt.text КАК 'SELECT *%';

Примеры использования расширенной процедуры sp_executesql

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

 sp_executesql @SQLStatement, N'@ParamNameDataType' , @Parameter1 = 'Value1'

Давайте начнем с простого примера, который показывает, как передать оператор и параметры:

 EXECUTE sp_executesql
               ВЫБЕРИТЕ *
                     ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50)', -- Определение параметра
             @AddressPart = 'а'; -- Значение параметра

В отличие от команды EXEC расширенная хранимая процедура sp_executesql повторно использует планы выполнения, если выполняется с тем же оператором, но с другими параметрами. Поэтому лучше использовать sp_executesql , а не EXEC команду :

 EXECUTE sp_executesql
               ВЫБЕРИТЕ *
                     ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50)', -- Определение параметра
             @AddressPart = 'а'; -- Значение параметра
ВЫПОЛНИТЬ sp_executesql
               ВЫБЕРИТЕ *
                     ОТ Лицо. Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50)', -- Определение параметра
             @AddressPart = 'б'; -- Значение параметра
ВЫБЕРИТЕ chdpln.objtype
, chdpln.cacheobjtype
, chdpln.usecounts
, sqltxt.текст
  ИЗ sys.dm_exec_cached_plans как chdpln
       ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ sys.dm_exec_sql_text(chdpln.plan_handle) как sqltxt
  ГДЕ sqltxt.text LIKE '%Person.Address%';

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

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

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

 СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ [dbo]. [test_dynSQL]
(
  @FirstName NVARCHAR(100) = NULL
 , @MiddleName NVARCHAR(100) = NULL
 , @LastName NVARCHAR(100) = NULL
)
КАК
НАЧИНАТЬ
УСТАНОВИТЬ БЕЗ СЧЕТА;
 
ОБЪЯВИТЬ @SQLExec NVARCHAR(MAX)
ОБЪЯВИТЬ @Параметры NVARCHAR(500)
 
SET @Parameters = '@FirstName NVARCHAR(100),
  @MiddleName NVARCHAR(100),
@Фамилия NVARCHAR(100)
'
 
УСТАНОВИТЕ @SQLExec = 'ВЫБЕРИТЕ *
ОТ Человека.Человека
ГДЕ 1 = 1
'
ЕСЛИ @FirstName НЕ NULL И LEN(@FirstName) > 0
   SET @SQLExec = @SQLExec + ' AND FirstName LIKE ''%'' + @FirstName + ''%'' '
ЕСЛИ @MiddleName НЕ NULL И LEN(@MiddleName) > 0
                SET @SQLExec = @SQLExec + ' AND MiddleName LIKE ''%''
                                                                    + @MiddleName + ''%'' '
ЕСЛИ @LastName НЕ NULL И LEN(@LastName) > 0
 SET @SQLExec = @SQLExec + ' AND LastName LIKE ''%'' + @LastName + ''%'' '
EXEC sp_Executesql @SQLExec
, @Параметры
 , @[электронная почта защищена], @[электронная почта защищена],
                                                @[электронная почта защищена]
 
КОНЕЦ
ИДТИ
EXEC [dbo]. [test_dynSQL] 'Ke', NULL, NULL

Параметр OUTPUT в sp_executesql

Мы можем использовать sp_executesql с параметром OUTPUT, чтобы сохранить значение, возвращаемое инструкцией SELECT. Как показано в приведенном ниже примере, это обеспечивает количество строк, возвращаемых запросом в выходную переменную @Output:

 DECLARE @Output INT
ВЫПОЛНИТЬ sp_executesql
        N'SELECT @Output = СЧЁТ(*)
            ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50), @Output INT OUT', -- Определение параметра
             @AddressPart = 'a', @Output = @Output OUT; -- Параметры
ВЫБЕРИТЕ @выход

Защита от внедрения кода SQL с помощью sp_executesql Процедура

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

Мы создаем простую хранимую процедуру и выполняем ее с допустимыми и недопустимыми параметрами:

 СОЗДАТЬ ИЛИ ИЗМЕНИТЬ ПРОЦЕДУРУ [dbo].[test_dynSQL]
(
  @InputTableName NVARCHAR(500)
)
КАК
НАЧИНАТЬ
  DECLARE @AddressPart NVARCHAR(500)
  DECLARE @Output INT
  ОБЪЯВИТЬ @SQLExec NVARCHAR(1000)
  ЕСЛИ СУЩЕСТВУЕТ (ВЫБЕРИТЕ 1 ИЗ sys.objects, ГДЕ тип = 'u' И имя = @InputTableName)
  НАЧИНАТЬ
      ВЫПОЛНИТЬ sp_executesql
        N'SELECT @Output = СЧЁТ(*)
            ОТ Лицо.Адрес
WHERE AddressLine1 LIKE ''%'' + @AddressPart + ''%''', -- Оператор SQL
              N'@AddressPart NVARCHAR(50), @Output INT OUT', -- Определение параметра
             @AddressPart = 'a', @Output = @Output OUT; -- Параметры
       ВЫБЕРИТЕ @выход
  КОНЕЦ
  ЕЩЕ
  НАЧИНАТЬ
     THROW 51000, 'Указано неверное имя таблицы, возможная SQL-инъекция. Выход из процедуры', 1
  КОНЕЦ
КОНЕЦ
EXEC [dbo].[test_dynSQL] 'Человек'

 EXEC [dbo].[test_dynSQL] 'NoTable'

Сравнение функций команды EXEC и хранимой процедуры sp_executesql

90 017

Команда EXEC	9006 7 Хранимая процедура sp_executesql
Без повторного использования плана кэширования	Повторное использование плана кэширования
Очень уязвима для SQL-инъекций	Гораздо менее уязвима для SQL-инъекций
Нет выходных переменных	Поддерживает выходные переменные
Без параметризации	Поддерживает параметризацию

Заключение

В этом сообщении продемонстрированы два способа реализации динамических функций SQL в SQL Server.