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.
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.
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
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