How to Pass Parameters in Dynamic T-SQL Query

Developers are often faced with the need to build a dynamic query, however, there are a number of pitfalls, which we will discuss further in this article.

Below is an example of a dynamic query:

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

All current variables are not visible (except the temporary tables) in a single block of code created by the Execute method.

Passing NULL

Pay an extra attention while passing variables with a NULL value. Any merger with NULL will result in NULL, therefore, instead of a query, you may receive an empty string.

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

Passing dates and times

The best format for passing dates is YYYYMMDD. Be prepared to the loss of precision while passing the time parameters. To preserve accuracy, it is better to pass values through a temporary table.

Passing parameters with floating decimal point also lead to accuracy loss.

Passing strings

All string values are potentially dangerous code. All single quotes inside a string must be duplicated. A string itself must be enclosed in single quotation marks.

Here is an example of an incorrect code:

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

Correct code:

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

Lists of values in the IN clause

While inserting a list of values to the IN clause, make sure you are not inserting an empty list. In this case, the section will look as follow ‘ field type IN () ‘ that will cause an error during compilation. The workaround is to include NULL at the beginning of the list or substitute an empty string to NULL. NULL can be compared to any data type. Such comparisons are always result in a negative result, however, the list, in this case, is guaranteed to be non-empty.

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

Here is an example of how to transfer 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(), 'String''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

Tricks of the trade

1. It is a good practice to declare variables for all parameters to be passed and then initialize these variables. Then you can use all these variables in the code. It increases the readability and makes debugging easier.

set @sql = 'select  + '
set @sql = replace(@sql, '', '1')
set @sql = replace(@sql, '', '2')
execute( @sql

2. To write parameterized dynamic queries it is better to use sp_executesql instead of executing. In this case, you can explicitly specify the type of variables and get protected from SQL injection attacks. You will not have any problems with dates, strings, numbers (with its rounding). Additionally, you will benefit from caching.

declare @i int
declare @paramDefinition nvarchar(500) = '@num int';
declare @sql nvarchar(100) = 'select @num'
 
EXECUTE sp_executesql @sql, @paramDefinition, @num = @i -- NULL will not
                                                        -- lead to error