Sometimes, you need to construct (n) number of result set from (n) number of inputs using single query and here the dynamic SQL concept comes handy. In some solutions, I really feel that it is very hard to ignore dynamic SQL. (Never ever execute the user input directly using dynamic SQL. Always validate it before execution.) But it has its own rule to construct and you must follow it.
Given below is the list of actions that you must take care while adding variable in the dynamic SQL.
- If you pass a NULL value of a variable in the dynamic SQL the whole string will be NULL, you must handle NULL variables in the dynamic SQL.
- If you pass any data type variables other than varchar/ nvarchar, it will generate error. You must convert it into varchar/nvarchar before using it into dynamic SQL.
- you must use concatenation sign ( + ) to concatenate the dynamic SQL with variables. You cannot place it directly inside the the dynamic SQL.
- You must be very careful while opening and closing single quotes (‘) in the dynamic SQL. If you forget even a single place you cannot execute dynamic SQL.
Given below are the two approaches. The old approach we usually follow in all versions of SQL Server and the new approach, we can follow in SQL Server 2012 and above. I came across this approach recently, built some dynamic SQL on it and found it very good.
Old Approach :
Given below is the script we usually use to construct dynamic SQL.
--This script is compatible with all versions of SQL Server DECLARE @SQL as varchar(max) DECLARE @Datetime as datetime SET @Datetime='2005-07-01 00:00:00.000' SET @SQL='SELECT * FROM Sales.SalesOrderHeader WHERE [OrderDate]=''' + CONVERT(varchar(50),@Datetime) + '''' PRINT @SQL EXEC (@SQL)
New Approach :
In the new approach, we need to use the CONCAT FUNCTION (shipped in SQL Server 2012) and it will resolve the above described problems.
Given below is the script.
--This script is compatible with SQL Server 2012 and above. DECLARE @SQL as varchar(max) DECLARE @Datetime as datetime SET @Datetime='2005-07-01 00:00:00.000' SET @SQL=CONCAT('SELECT * FROM Sales.SalesOrderHeader WHERE [OrderDate]=','''',@Datetime,'''') PRINT @SQL EXEC (@SQL)
The performance will be same for both approaches but the new approach will be the shortest way to write dynamic SQL because it handles the above dynamic SQL issues internally. Also, it will be easier to debug as well because it will be in parts so you can easily split at the time of debugging.
Let me know your opinion about new approach and its pros and cons.