Dynamic T-SQL and Benefits of its Usage
In our projects, we have to cope with different tasks. To solve some of them, we use dynamic T-SQL.
Why do we need dynamic T-SQL? Well, it is up to you.
In one of the projects, we have solved the task of building dynamic reports, and in others — data migration. Dynamic T-SQL is essential when you need to create, modify, get data or objects, but values or names come as parameters. For sure, it may seem unreasonable. Still, such tasks are possible. Later in the article, we will see several examples.
You can execute a dynamic command in several ways:
- Using the EXEC/EXECUTE keyword;
- Using the sp_executesql stored procedure.
These ways differ greatly. We will try to explain this difference on the particular example.
DECLARE @sql varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = ‘CustomerID, ContactName, City’
SET @city = ‘London’
SELECT @sql = ‘ SELECT CustomerID, ContactName, City ‘+
‘ FROM dbo.customers WHERE 1 = 1 ‘
SELECT @sql = @sql + ‘ AND City LIKE ‘’’ + @city + ‘’’’
EXEC (@sql)
As you can see, we create a dynamic command. If we execute select @sql, we will get the following result:
SELECT CustomerID, ContactName, City FROM customers WHERE City = ‘London’
What is wrong here? The query works well and everyone should be satisfied. Still, there are some reasons why you should not do this:
1. When typing a command, it is very easy to make a mistake with a number of single quotes “ ’ ”, as it is necessary to specify additional single quotes to pass a text value into the query.
2. Because of SQL injections. For example, for @city you can specify the following:
set @city = ‘’’DROP TABLE customers — ‘’’
The result is going to be horrible, as soon as both SELECT and DROP TABLE customers may be successfully executed.
3. You may face the situation when you have several variables containing codes of your commands. For example, EXEC(@sql1 + @sql2 + @sql3).What difficulties may occur here?It is necessary to remember that each command works separately. However, it may seem that the concatenation operation (@sql1 + @sql2 + @sql3) will be completed, and then the general command will be executed. In addition, you need to remember EXEC parameter is restricted to 4000 symbols.
4. An implicit type casting takes place, as parameters are passed as strings.
What will change if we use sp_executesql? It is easier for a developer to write and debug code, as it looks like a standard SQL query.
DECLARE @sqlCommand varchar (1000)
DECLARE @columnList varchar (75)
DECLARE @city varchar (75)
SET @city = ‘London’
SET @sqlCommand = ‘SELECT CustomerID, ContactName, City FROM customers WHERE City = @city’
EXECUTE sp_executesql @sqlCommand, N’@city nvarchar(75)’, @city = @city
What has changed?
- Type casting is not required.
- No problems with “ ‘ ”.
- No problems with SQL Injections.
Query plans are cached for both methods, however, they have differences. To check these differences, refer to the picture 1 and picture 2.
In order to go deeper with this case and to continue reading, follow the link https://goo.gl/6kcsor.
Thank you for reading.
