Conversion Of Dynamic SQL: to automate or not to automate

Alex Kirpichny

Alexandr Kirpichny

Chief Product Officer, Ispirer Systems

Usually when we describe tools that Ispirer System provides, we say that they can convert almost everything, and it's true. The tools can be adopted to convert all the possible cases that clients might have in their project except some specific cases that are connected with dynamic SQL.

In this article I would like to talk about one of the most complex cases for dynamic SQL migration using SQLWays Wizard. And I'll try to describe some cases that our tool can handle and cases that are not supported by the tool.

Preparation

Let me start from the very beginning and explain what is the dynamic SQL and how it looks like.

So, dynamic is a programming technique that allows you to construct SQL statements dynamically at runtime. It means that statements will be build with the help of constant strings, variables and parameters. And the final statement that will be executed totally depends on the values that are stored in the variables and parameters that are used in dynamic SQL.

Dynamic SQL can be extremely useful when you need to build dynamic reports or when you need to alter\create\modify objects or data, but the names of the objects are unknown and will be passed with parameters or variables. You can also use dynamic SQL where static SQL does not support the operation you want to perform. Below you can see an example of dynamic SQL statement that is built using only one variable:

DECLARE
@table NVARCHAR(128),
@sql NVARCHAR(MAX);
SET @table = N'dbo.test'; -- in variable @table we set a table name
SET @sql = N'SELECT * FROM ' + @table; -- prepare dynamic statement by concatenating SELECT part and a variable with table name
EXEC sp_executesql @sql; -- in this EXEC statement we execute dynamic sql

Now, let me try to explain in detail how our sql converter identifies a dynamic SQL in the sample provided.

In the course of analyzing this piece of code our tool will check\parse\analyze all the statements one by one from the beginning. DECLARE statement with variables will go first, then SET statements will follow. Please, note that at this point our migration tool is not able to understand that the dynamic SQL statement is used in the second SET statement. It will be found out on the next step, when parsing EXEC statement. So, when parsing EXEC statement SQLWays Wizard will see that system procedure "sp_executesql" is called, and it "knows" that this procedure works with dynamic sql statements. Sql converter expects that this EXEC will contain a constant string with SQL code or variable that stores an SQL statement that should be executed. In our sample we have @sql variable that stores the statement.

So, after that, the migration tool will go back and try to find all the SET statements that assign values to this variable (@sql variable in our case). There is only one such statement in the sample and the tool "knows" that in this assignment is used sql statement, so it treats this string as sql statement:

'SELECT * FROM ' + @table

The tool parses this line as follows:

[part of sql statement] + [identifier_name or expression] 

Where [part of sql statement] is a constant string from SET statement and [identifier_name or expression] is an identifier name or expression. Then SQLWays Wizard tries to convert that construction by trying to read and check whether the statement is built correctly and converts it. As the code is syntactically correct, the migration tool will be able to convert it.

Gears

Now let's review a more complex sample of dynamic SQL:

DECLARE @birthYear int = 1970;
DECLARE @statement NVARCHAR(4000);
SET @statement = ' SELECT JobTitle, Count(BusinessEntityID)'
SET @statement = @statement + ' FROM Employee_info'
SET @statement = @statement + ' WHERE Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR)
SET @statement = @statement + ' GROUP BY JobTitle'
EXECUTE sp_executesql @statement

In this sample we can see that the dynamic statement is built with multiple SET statements. As we know the Ispirer sql converter will see that sp_executesql procedure is used with @statement, so it will check all the SET statements, starting from the first one, and collect all the values in these statements in one query. So the collected query will look like:

' SELECT JobTitle, Count(BusinessEntityID)
FROM HumanResources.Employee
WHERE Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR)
' GROUP BY JobTitle'

And the tool will see this construction as:

[part of sql statement] + [identifier_name or expression] + [part of sql statement]

Using clever parser technology the Ispirer migration tool will check if the structure and SQL syntax of the query built are correct: there is a SELECT part, FROM part, WHERE clause with dynamically added value and a GROUP BY clause, and all these parts are located correctly. After that the dynamic SQL migration is implemented automatically.

Integration

Let's discuss another example:

DECLARE @birthYear int = 1970
DECLARE @statement NVARCHAR(4000)
SET @statement = ' SELECT JobTitle, Count(BusinessEntityID)'
SET @statement = @statement + ' FROM Employee_info'
IF @birthYear != 0
SET @statement = @statement + ' WHERE Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR)
ELSE
SET @statement = @statement + ' WHERE Year(BirthDate) > 1900 '
SET @statement = @statement + ' GROUP BY JobTitle'
EXECUTE sp_executesql @statement

This sample differs from the previous one by the only one IF ELSE construction. But this construction makes a great difference.

As we already know our sql converter will find all the SET statements. See below what it will get:

' SELECT JobTitle, Count(BusinessEntityID)
FROM Employee_info
WHERE Year(BirthDate) = ' + CAST(@birthYear as NVARCHAR)
' WHERE Year(BirthDate) > 1900
GROUP BY JobTitle'

Construction of this query is the same as the previous one:

[part of sql statement] + [identifier_name or expression] + [part of sql statement]

But with this query, SQLWays Wizard will fail when trying to check it, because the structure of this query is not correct. As there are two WHERE clauses, the migration tool will fail on parsing this query since its structure is incorrect.

So the SQL converter tries to build a dynamic query without taking into account the IF ELSE and CASE constructions.

To conclude, I'd like to point out the main idea of the article. If you want to understand whether the dynamic query can be converted automatically or not, just try to collect all the pieces of dynamic statements in one query without taking into account IF ELSE and CASE construction. More so, check whether the syntax and structure of built query is correct. If yes, then it can be converted, if no, then SQLWays Wizard will not be able to convert it automatically and manual corrections will be required for SQL migration.

Discover more about Ispirer products and solutions!

Find Out More