Overview
Dynamic SQL (code that is executed dynamically), like cursors and temporary tables, is another area of T-SQL that should be used with care and wisdom. It has the potential to be used in an unsafe way and could lead to serious security breaches and code that performs badly and is difficult to maintain. On the other hand, when used wisely, dynamic SQL can help you achieve things that would be difficult to achieve any other way. And there are cases in which it is the only way you can provide good performance. In short, using dynamic SQL requires programmatic maturity.
|
Note |
I'll discuss some of the potential security breaches involved with dynamic SQL later in the chapter under the section "SQL Injection." SQL Injection is a hacking technique that is used to "plant" malicious code through strings that are later parsed and executed by SQL Server. I strongly recommend that you research the subject thoroughly before using dynamic SQL in both production and test or development environments. A lot of information about the subject is available on the Internet and in other resources. There's also a short but excellent article about dynamic SQL in Books Online under "Security Considerations for SQL Server/SQL Injection," also available via the following URL:
|
Dynamic SQL is typically required when you must construct sections of your code based on user input. This is exactly why it is so dangerous; it is like allowing customers in a store to use the cash register themselves to ring up their own purchases and make their own change. Using dynamic SQL where you concatenate your code strings based on user inputs amounts to letting users write your code, and it is extremely difficult to do it safely. I've made the effort to demonstrate some techniques to protect against security breaches involved with SQL Injection; but even if you let security experts review your code, they will tell you that it's almost impossible to protect against SQL Injection attempts when incorporating user input in your code. Static code can have some dynamic elements, but only when the dynamic part is an expression that returns a scalar value. For example, you can incorporate an input parameter for a filter expression in a static query. In other words, parameterization, handled correctly, encapsulates and type-checks the user inputs.
Microsoft SQL Server provides you with two commands that invoke code strings that you construct dynamically—EXEC (short for EXECUTE) and sp_executesql.
|
Note |
Note that the EXEC command has two uses; one to invoke a stored procedure: EXEC <procedure name and arguments>, and the other to invoke dynamic code: EXEC(<string>). When discussing EXEC in this chapter, I'll be referring to the latter unless explicitly stated otherwise. |
As a rule, sp_executesql is preferable because it has an interface (input/output parameters) and EXEC doesn't. With sp_executesql, you are more likely to reuse execution plans because you can more easily generate a query string that you invoke repeatedly—the same query string with different input values as arguments in each invocation. Also with sp_executesql you can write safer code, as I will explain later in the chapter. Still, EXEC is more flexible in certain cases, which I will describe in this chapter. So remember that unless you have a compelling reason to use EXEC, stick with using sp_executesql.
Before I delve into EXEC, sp_executesql, and their characteristics and applications, I'd like to briefly mention some important aspects of working with dynamic SQL in general:
-
Dynamic SQL requires that the user executing the code have direct permissions to execute it even if the code is within a stored procedure. That is, if you provide a user with EXECUTE permissions on a routine and the routine invokes dynamic code, the user is still required to have direct permissions to run the code that is invoked dynamically. This limitation is relaxed in SQL Server 2005 because in that version of SQL Server you can impersonate the user and allow the code to run under any security context that you like, and that context will apply to all activities. This is achieved by using the new EXECUTE AS clause, which I will describe in Chapter 7.
-
Dynamic SQL operates in a batch that is separate from the calling batch. This means that the dynamic batch is parsed, resolved, and optimized as a separate unit. This aspect of dynamic SQL can be a drawback because you end up with another unit of compilation. But when used wisely, it can actually be beneficial to you. I'll demonstrate how you can take advantage of this behavior later in the chapter.
-
Environmental settings of the calling batch—such as the database context, session options, and the like—are in effect for all inner levels in the call stack (dynamic batch, stored procedure call, and so on). For example, if you change the database context of a calling batch with the USE <database_name> command, the new database context is in effect for a dynamic batch. However, changes made to environmental settings within a dynamic batch are not in effect for outer levels once the dynamic batch goes out of context. I'll demonstrate this behavior later in the chapter.
-
Very similar to environmental settings, a local temporary table created in a calling batch is visible to inner levels, including a dynamic batch. However, a local temporary table created in an inner level is not visible to outer levels. As soon as the creating level goes out of scope, the local temporary table is automatically destroyed. For details about this behavior, please refer to Chapter 2.
-
Unlike environmental settings and temporary tables, a local variable is visible only to the batch where it was declared. They are not visible to inner levels. I'll also demonstrate this behavior later in the chapter.
So without further ado, let's delve into EXEC, sp_executesql, and the uses of dynamic SQL.
