Conversion of Dynamic SQL with Ispirer Toolkit

Alexander Kirpichny

Product Owner, Ispirer Systems

Tools that automate migration have always found dynamic SQL conversion challenging. This article will examine how tools such as the Ispirer Toolkit can simplify this task.

What is dynamic SQL?

Let's start with the basics. Dynamic SQL is a programming technique that allows you to write SQL statements dynamically at runtime. This means statements are built using constant strings, variables, and parameters. The final statement that is executed depends on the values stored in the variables and parameters used in dynamic SQL.

Dynamic SQL is a Lego construction for your code, as it is prepared statements in any programming language. You can write down any query using dynamic SQL - both simple and complex.

Static SQL, on the other hand, uses ready-defined SQL statements embedded directly in the code. As they are compiled once and reused, it is easier to optimize queries, which often leads to better performance. Static SQL is considered to be more secure because the query structure doesn't change during runtime. One drawback, however, is its lack of flexibility in applications that need varied and complex query requirements.

Benefits of dynamic SQL

Dynamic SQL provides several benefits. Let's examine the main ones:

• Flexibility. One of the most significant advantages of dynamic SQL is its flexibility. You can build queries on the fly based on user input or application needs, which is particularly useful in BI for creating custom reports or handling complex analytical tasks.
• Prediction optimization. An essential advantage of flexible SQL is that the applications generated in each query are customized for the current descriptions used. A big problem with standalone SQL solutions is that the extra predictions confuse the query editor, causing us to create inefficient programs. Dynamic SQL solves this problem without adding anything to the issue.
• Single Query Plan Caching. In each database, there's one cached query system with additional ad-program time for every process request (this may be verified using the view sys.dm_exec_cached_plans). This implies that each time a brand new conflict is transferred to a stored course, mergers occur, which can obviously kill performance. Flexible query isn't a parameter, producing duplicate query programs for various arguments.

Use cases of dynamic SQL

Dynamic SQL is used in various scenarios. The main ones include the following use cases:

• An online shopping website needs to retrieve products based on user search queries. The website can formulate an SQL query in response to the user's input by applying dynamic SQL. For instance, if the user looks for products of a particular brand, the website can generate an SQL query to fetch all products of that brand. Similarly, if the user is seeking products within a specific price bracket, the website can create an SQL query to pull products within that bracket.
• Medical software enables physicians to look up patient files using parameters like name, age, sex, medical background, and so on. Using Dynamic SQL, the software can build the SQL query according to the data given by the physician. For instance, if the physician is looking for patients suffering from a particular medical issue, the software can formulate the SQL query to fetch all patient files corresponding to that issue.

Migration of dynamic SQL with Ispirer Toolkit

Ispirer Toolkit handles the migration of dynamic SQL. Let's review in detail how that happens.

Here is an example of dynamic SQL 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

Let's start with understanding how Ispirer Toolkit identifies the dynamic SQL in the sample provided.

While analyzing this piece of code, Ispirer Toolkit checks, parses, and analyzes all the statements one by one from the beginning. DECLARE statement with variables goes first, then SET statements follow. Please note that at this point, Ispirer Toolkit cannot understand why the dynamic SQL statement is used in the second SET statement. It will be found in the next step when parsing the EXEC statement. So, when parsing the EXEC statement, SQLWays Wizard understands what the system procedure "sp_executesql" is called, and it "knows" that this procedure works with dynamic SQL statements. SQL converter expects this EXEC to contain a constant string with SQL code or variable that stores an SQL statement that should be executed. In our sample, we have an @sql variable that stores the statement.

Following that, the migration tool returns and tries 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 this assignment uses an SQL statement, so it treats this string as an 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 can convert it.

Migration of a complex sample of dynamic SQL

Now let's see how Ispirer Toolkit handles a more complex dynamic SQL conversion:

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

The sample above shows that the dynamic statement is built with multiple SET statements. As we know, Ispirer Toolkit will understand 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. The collected query will look like this:

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

The tool will see this construction in the following way:

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

Using smart parser technology, SQLWays Wizard will check if the structure and SQL syntax of the query built is correct. There is a SELECT part, a FROM part, a 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.

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 only using the IF ELSE construction, but this construction makes a significant difference.

As it has already been said, SQLWays Wizard will first 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'

The 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 wrong. There are two WHERE clauses, so the migration tool will fail to parse this query since its structure is incorrect. So, the SQL converter tries to build a dynamic query without considering the IF ELSE and CASE constructions.

Wrapping up

As you can see, migrating dynamic SQL is not easy. However, this task does not have to be difficult with the right tools. It offers up to 100% automated migration of your entire database schema and data to modern technologies. Are you interested in learning more or purchasing a migration solution? Check out the Ispirer Toolkit pricing to find the perfect plan for your needs.

Are you not sure which plan is the right fit? Book a demo with our Ispirer experts and get the answers to all of your questions in a quick 30-minute call.