Dynamic T-SQL and Benefits of its Usage

Image for post
Image for post

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)

SELECT CustomerID, ContactName, City FROM customers WHERE City = ‘London’

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 — ‘’’

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

  1. Type casting is not required.
  2. No problems with “ ‘ ”.
  3. 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.

Written by

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store