Chief Product Officer, Ispirer Systems
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]
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]
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.