– SQL that is constructed by putting together strings to form executable code that gets parsed for the first time by the compiler at run-time.
Justification for Dynamic SQL?
There should be relatively few instances where dynamic SQL is the only option. Generally, regular SQL whose syntax can be checked by the compiler at compile time is the right way to do things for various reasons.
In SQL Server, here are some situations where one is forced to use dynamic SQL
- Entities and/or nature of operation is dynamic in nature and cannot be defined/known at design time.
- e.g.,: A procedure that converts all columns named EMPID in all tables from INT to BIGINT
- A centralized coding mechanism is needed (like a SP in master) and it needs to operate on any database within the instance.
- e.g.,: A centralized partitioning framework or a rule engine
- The code may work against multiple databases to aggregate information.
- e.g.,: Collect size information from each database and aggregate up to instance level
Some of the reasons not to do dynamic SQL
- No design time syntax checking
- No design time permissions checking
- Unless done correctly (with parameters), dynamic SQL will not be stored in the plan cache and a compilation cost will be incurred for every execution
- Scoping and sharing issues for local variables and data between dynamic and non-dynamic code is very difficult
- SQL injection is very real
- Quoted strings in SQL need extra care
- Unless formatted properly, dynamic SQL is very hard to follow and formatting tools do not operate on the literal portion of the dynamic SQL.
- Program might exhibit erratic behavior that is parameter value specific
Still need to use Dynamic SQL?
With the reiteration that the use of dynamic SQL itself should be very rare in a business application, the following example illustrates a bad way of doing dynamic SQL and the good way to do the same
Improper usage of dynamic SQL:
Reasons for being bad:
- What if CreateUserId had quotes in itself?
- What if someone passed a injected SQL for the parameter value to delete from other tables?
- The dynamic length of the SQL would prevent it from having a unique entry in the plan cache for the SQL involved
DECLARE @ClientCode VARCHAR(6); DECLARE @CreateUserId VARCHAR(20); DECLARE @SQL NVARCHAR(255); SET @ClientCode = 'H%'; SET @CreateUserId = '<MY_USER_ID>'; SET @SQL = 'SELECT * FROM Customer_Master ' + 'WHERE ClientCode LIKE ''' + @ClientCode + ''' AND CreateUserId = ''' + @CreateUserId + ''''; EXEC sp_ExecuteSQL @SQL;
Proper use of dynamic SQL
Please note that the business usage is still bad because it should have been done without dynamic SQL but the parameterization aspect is the good part.
DECLARE @ClientCode VARCHAR(6); DECLARE @CreateUserId VARCHAR(20); DECLARE @Params NVARCHAR(50); DECLARE @SQL NVARCHAR(255); SET @ClientCode = 'H%'; SET @CreateUserId = '<MY_USERID>'; SET @Params = '@ClientCode VARCHAR(6), @CreateUserId VARCHAR(20)'; SET @SQL = 'SELECT * FROM Client_Master ' + 'WHERE ClientCode LIKE @ClientCode ' + ''' AND Create_UserId = @CreateUserId '; EXEC sp_ExecuteSQL @SQL, @Params, @ClientCode, @CreateUserId;
Both of the above produce the same results but the second one does not have the disadvantages that the first one does. Notice that the length of the parsed SQL will never change across calls so, the query will be compiled and placed in the plan cache once and be reused on subsequent calls. In addition to being cleaner, it is also safer because we use concrete variable data types to which the parameters should conform to.
Pay close attention to the “EXEC sp_ExecuteSQL” call.
- The first parameter is the SQL statement itself.
- The second one is the list of parameters with their data types.
- The third and the fourth are the actual parameter values that are passed in.
- The call can have N number of parameters (the above example has just 2). Also, the parameters can be OUT parameters.
In other words, this is the equivalent of bind variables in Oracle.
Further reading – The curse and blessings of dynamic SQL