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:

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.

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:

Correct code:

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.

Here is an example of how to transfer complex parameters through a temporary table:

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.

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

Andrey Langovoy

Andrey Langovoy is a team leader at Devart. He takes part in development and testing database management tools for SQL Server, writes articles about SQL Server and contributes to open source projects, MSDN and MDN.
Andrey Langovoy