Passing parameters to a dynamic query in T-SQL

I have repeatedly come across the need to build a dynamic query, and here there are a number of pitfalls that I will discuss below. An example of a dynamic query:

declare @sql varchar(100) = 'select 1+1' execute( @sql) 

1. Running a line through Execute creates a separate block of code in which the current variables will not be visible, but all temporary tables are visible.

2. Note the transfer of variables with a NULL value. Any merging with NULL will result in NULL, therefore, instead of a query, you can get an empty string.
')

 declare @i int declare @sql varchar(100) = 'select ' + cstr(@i) execute( @sql ) --  

3. Passing dates and times. Dates are best transmitted in the format YYYYMMDD. When transferring parameters over time, attention should be paid to the loss of accuracy. To maintain accuracy, it is better to pass the values ​​through a temporary table.

4. Passing parameters with a floating decimal point has the same problems as passing time inside a constructed query.

5. String values ​​- potentially dangerous code. To start inside the string, all single quotes must be duplicated. The string itself is enclosed in single quotes.

Error code example:

 Declare @str varchar(100) = 'Number ''1'' ' Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + @str + '''', 'null' ) Execute( @sql ) --     

The correct code is:

 Declare @str varchar(100) = 'Number ''1'' ' Declare @sql varchar(1000) = 'select String = '+ IsNull( '''' + replace( @str, '''', '''''') + '''', 'null' ) Execute( @sql ) 

6. Substitution of lists in the section IN. The main danger is an empty list. In this case, the section will be of the type 'field IN ()', which, when compiled, will cause an error. As a method of struggle: always include NULL in the top of the list or replace the empty string with NULL. NULL can be compared with any data type. A comparison with NULL always gives a negative result, but the list is guaranteed not empty.

 Declare @list varchar(100) = '' iif @list = '' set @list = 'null' Declare @sql varchar(1000) = 'select number from documents where id in ('+@list+') ' Execute( @sql ) 

Here is an example of the safe transmission of complex parameters through a temporary table:

 if OBJECT_ID('tempdb..#params') is not null drop table #params create table #params ( v1 int, v2 datetime, v3 varchar(100) ) insert #params values ( 1, getdate(), ' ''1''') declare @sql varchar(1000) = ' declare @v1 int, @v2 datetime, @v3 varchar(100) select @v1 = v1 , @v2 = v2, @v3 = v3 from #params select @v1, @v2, @v3 ' execute(@sql) drop table #params 

Well, for a snack, little tricks:

It is better to first pass the passed parameters through variables, initialize these variables and use these variables during calculations. In this case, the readability of the query text increases and it is easier to debug it.

If you do without variables, you can use the following method:

 set @sql = 'select <VAR1> + <VAR2>' set @sql = replace(@sql, '<VAR1>', '1') set @sql = replace(@sql, '<VAR2>', '2') execute( @sql ) 

In addition to the above features, there are a couple of ways to pass parameters:
1. Use sp_executesql (as suggested to me correctly in the comments)
2. Wrap the query in a temporary stored procedure and run it. With a large number of launches, this method is even more efficient.

 declare @sql varchar(200) = ' create procedure #test ( @p1 int, @p2 int) as select @p1 + @p2' execute( @sql ) exec #test 1, 2 exec #test 3,4 drop procedure #test