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

Tsql теория > Динамический SQL
28.05.2018 17:37:33


Наиболее часто встречающиеся слова в статье:

[DECLARE] [nvarchar] [sp_executesql] [ParmDefinition] [FirstName] [plan_handle] [IntVariable] [BusinessEntityID] [VARCHAR] [SQLString]


Статья:

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

DECLARE @IntVariable int;  
DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  
 
/* Build the SQL string one time.*/  
SET @SQLString =  
     N''SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID  
       FROM AdventureWorks2012.HumanResources.Employee   
       WHERE BusinessEntityID = @BusinessEntityID'';  
SET @ParmDefinition = N''@BusinessEntityID tinyint'';  
/* Execute the string with the first parameter value. */  
SET @IntVariable = 197;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @BusinessEntityID = @IntVariable;  
/* Execute the same string with the second parameter value. */  
SET @IntVariable = 109;  
EXECUTE sp_executesql @SQLString, @ParmDefinition,  
                      @BusinessEntityID = @IntVariable;  

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

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
  1. В отличие от EXECUTE при использовании sp_executesql, не нужно никакое приведение типов, если мы используем типизированные параметры sp_executesql.
  2. Это решает проблему с дополнительными «’».
  3. Решается проблема безопасности — Sql инъекции (SQL Injection).
  4. «параметризация», вот хороший пример:

USE AdventureWorks2012
GO

DBCC FREEPROCCACHE

DECLARE
      @str VARCHAR(MAX) = ''SELECT * FROM Person.Person WHERE FirstName = ''
    , @param VARCHAR(50) = ''David''

EXEC (@str + '''''''' + @param + '''''''')

SET @param = ''Tom''

EXEC (@str + '''''''' + @param + '''''''')

SELECT st.[text], cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.[text] LIKE ''%SELECT * FROM Person.Person%''
    AND st.[text] NOT LIKE ''%select st.text%''

GO

DBCC FREEPROCCACHE

EXEC sys.sp_executesql
    N''SELECT * FROM Person.Person WHERE FirstName = @val'',
    N''@val VARCHAR(200)'',
    ''David''

EXEC sys.sp_executesql
    N''SELECT * FROM Person.Person WHERE FirstName = @val'',
    N''@val VARCHAR(200)'',
    ''Tom''

SELECT st.[text], cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.[text] LIKE ''%SELECT * FROM Person.Person%''
    AND st.[text] NOT LIKE ''%select st.text%''

Если кратко, то когда параметризации нет (пример EXEC), с каждым новым значением параметра будет свой план генерироваться:

text                                                         plan_handle
------------------------------------------------------------ ----------------------------------------------
SELECT * FROM Person.Person WHERE FirstName = ''Tom''          0x06000B00CA31691380824B12020000000100000000
SELECT * FROM Person.Person WHERE FirstName = ''David''        0x06000B007BF4203740804B12020000000100000000

и когда параметризация есть (один план на «все случаи жизни», который формируется на основе значений при первом выполнении):

text                                                                        plan_handle
--------------------------------------------------------------------------- ----------------------------------------
(@val VARCHAR(200))SELECT * FROM Person.Person WHERE FirstName = @val       0x06000B00632CDD0440804B12020000000100

Такие вот нюансы сильно на производительность влияют.В любом случае, для написания параметризированных динамических запросов лучше использовать sp_executesql, а не execute. Тогда вы сможете явно указать типы переменных и защититесь от SQL-инъекций. У вас пропадут проблемы со строками, датами, числами и их округлением + кеширование заработает.
declare @i int 
declare @paramDefinition nvarchar(500) = ''@num int'';
declare @sql nvarchar(100) = ''select @num''
 
EXECUTE sp_executesql @sql, @paramDefinition, @num = @i -- NULL не приводит к ошибке
 

Как получить результат sp_executesql в переменную?

Если у вас есть параметры OUTPUT, вы можете сделать
 
DECLARE @retval int   
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
 
DECLARE @tablename nvarchar(50)  
SELECT @tablename = N''products''  
 
SELECT @sSQL = N''SELECT @retvalOUT = MAX(ID) FROM '' + @tablename;  
SET @ParmDefinition = N''@retvalOUT int OUTPUT'';
 
EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
 
SELECT @retval;