Error Handling and Dynamic SQL
Prior to SQL Server 2005, error handling was limited almost exclusively to the @@error system function and the RAISERROR statement, or through client-side exception handling. T-SQL in SQL Server 2012 still provides access to these tools, but it also supports modern structured error handling similar to that offered by other high-level languages such as C++, C#, and Visual Basic. In this chapter, we will discuss legacy T-SQL error-handling functionality and the newer structured error-handling model in T-SQL. This chapter introduces tools useful for debugging server-side code, including T-SQL statements and the Visual Studio IDE.
We will also discuss dynamic SQL in this chapter, which is often more difficult to debug and manage than standard (nondynamic) T-SQL statements. Dynamic SQL, while a useful tool, also has security implications, which we will address.
Error Handling
SQL Server 2012 provides improvements in error handling over SQL Server 2008 and prior releases. In this section, we’ll discuss legacy error handling, SQL Server 2008 TRY . . . CATCH structured error handling, as well as the new SQL 2012 THROW statement.
Note It may seem odd to still be referring in 2012 to an error handling mechanism introduced in SQL Server 2000. The reality is you are still quite likely to encounter the @@error statement in much of your code and, despite certain limitations and restrictions, the @@error statement still proves useful for error handling.
In SQL Server 2000, the primary method of handling exceptions was through the @@error system function. This function returns an int value representing the current error code. An @@error value of 0 means no error occurred. One of the major limitations of this function is that it is automatically reset to 0 after every successful statement. This means you cannot have any statements between the code that you expect might produce an exception and the code that checks the value of @@error. This also means that after @@error is checked, it is automatically reset to 0, so you can’t both check the value of @@error and return @@error from within an SP. Listing 17-1 demonstrates an SP that generates an error and attempts to print the error code from within the procedure and return the value of @@error to the caller.
Listing 17-1. Incorrect Error Handling with @@error
CREATE PROCEDURE dbo.TestError (@e int OUTPUT)
AS
BEGIN
INSERT INTO Person.Person(BusinessEntityID)
VALUES (1);
PRINT N'Error code in procedure = ' + CAST(@@error AS nvarchar(10));
SET @e = @@error;
END
GO
DECLARE @ret int,
@e int;
EXEC @ret = dbo.TestError @e OUTPUT;
PRINT N'Returned error code = ' + CAST(@e AS nvarchar(10));
PRINT N'Return value = ' + CAST(@ret AS nvarchar(10));
The TestError procedure in Listing 17-1 demonstrates one problem with @@error. The result of executing the procedure should be similar to the following:
Msg 515, Level 16, State 2, Procedure TestError, Line 4
Cannot insert the value NULL into column 'PersonType', table
'AdventureWorks.Person.Person'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Error code in procedure = 515
Returned error code = 0
Return value = −6
As you can see, the error code generated by the failed INSERT statement is 515 when printed inside the SP, but a value of 0 (no error) is returned to the caller via the OUTPUT parameter. The problem is with the following line in the SP:
PRINT N'Error code in procedure = ' + CAST(@@error AS nvarchar(10));
The PRINT statement automatically resets the value of @@error after it executes, meaning you can’t test or retrieve the same value of @@error afterward (it will be 0 every time). The workaround is to store the value of @@error in a local variable immediately after the statement you suspect might fail (in this case the INSERT statement). Listing 17-2 demonstrates this method of using @@error.
Listing 17-2. Corrected Error Handling with @@error
CREATE PROCEDURE dbo.TestError2 (@e int OUTPUT)
AS
BEGIN
INSERT INTO Person.Person(BusinessEntityID)
VALUES (1);
SET @e = @@error;
PRINT N'Error code in procedure = ' + CAST(@e AS nvarchar(10));
END
GO
DECLARE @ret int,
@e int;
EXEC @ret = dbo.TestError2 @e OUTPUT;
PRINT N'Returned error code = ' + CAST(@e AS nvarchar(10));
PRINT N'Return value = ' + CAST(@ret AS nvarchar(10));
By storing the value of @@error immediately after the statement you suspect might cause an error, you can test or retrieve the value as often as you like for further processing. The following is the result of the new procedure:
Msg 515, Level 16, State 2, Procedure TestError2, Line 4 Cannot insert the value NULL into column 'PersonType', table 'AdventureWorks.Person.Person'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Error code in procedure = 515
Returned error code = 515
Return value = −6
In this case, the proper @@error code is both printed and returned to the caller by the SP. Also of note is that the SP return value is automatically set to a nonzero value when the error occurs.
The RAISERROR statement is a T-SQL statement that allows you to throw an exception at runtime. The RAISERROR statement accepts a message ID number or message string, severity level, state information, and optional argument parameters for special formatting codes in error messages. Listing 17-3 uses RAISERROR to throw an exception with a custom error message, a severity level of 17, and a state of 127.
Listing 17-3. Raising a Custom Exception with RAISERROR
RAISERROR ('This is an exception.', 17, 127);
When you pass a string error message to the RAISERROR statement, as in Listing 17-3, a default error code of 50000 is raised. If you specify a message ID number instead, the number must be between 13000 and 2147483647, and it cannot be 50000. The severity level is a number between 0 and 25, with each level representing the seriousness of the error. Table 17-1 lists the severity levels recognized by SQL Server.
Table 17-1. SQL Server Error Severity Levels
| Range | Description |
|---|---|
| 0–10 | Informational messages |
| 11–18 | Errors |
| 19–25 | Fatal errors |
Tip Only members of the sysadmin fixed server role of users with ALTER TRACE permissions can specify severity levels greater than 18 with RAISERROR, and the WITH LOG option must be used.
The state value passed to RAISERROR is a user-defined informational value between 1 and 127. The state information can be used to help locate specific errors within your code when using RAISERROR. For instance, you can use a state of 1 for the first RAISERROR statement in a given SP and a state of 2 for the second RAISERROR statement in the same SP. The state information provided by RAISERROR isn’t as necessary in SQL Server 2012 since you can retrieve much more descriptive and precise information from the functions available in CATCH blocks.
The RAISERROR statement supports an optional WITH clause for specifying additional options. The WITH LOG option logs the error raised to the application log and the SQL error log, the WITH NOWAIT option sends the error message to the client immediately, and the WITH SETERROR option sets the @@error system function (in a CATCH block) to an indicated message ID number. This should be used with a severity of 10 or less to set @@error without causing other side effects (e.g., batch termination).
RAISERROR can be used within a TRY or CATCH block to generate errors. Within the TRY block, if RAISERROR generates an error with severity between 11 and 19, control passes to the CATCH block. For errors with severity of 10 or lower, processing continues in the TRY block. For errors with severity of 20 or higher, the client connection is terminated and control does not pass to the CATCH block. For these high-severity errors, the error is returned to the caller.
Try . . . Catch Exception Handling
SQL Server 2012 supports the TRY . . . CATCH model of exception handling common in other modern programming languages and first introduced in SQL Server 2008. In the T-SQL TRY . . . CATCH model, you wrap the code you suspect could cause an exception in a BEGIN TRY . . . END TRY block. This block is immediately followed by a BEGIN CATCH . . . END CATCH block that will be invoked only if the statements in the TRY block causes an error. Listing 17-4 demonstrates TRY . . . CATCH exception handling with a simple SP.
Listing 17-4. Sample TRY . . . CATCH Error Handling
CREATE PROCEDURE dbo.TestError3 (@e int OUTPUT)
AS
BEGIN
SET @e = 0;
BEGIN TRY
INSERT INTO Person.Address (AddressID)
VALUES (1);
END TRY
BEGIN CATCH
SET @e = ERROR_NUMBER();
PRINT N'Error Code = ' + CAST(@e AS nvarchar(10));
PRINT N'Error Procedure = ' + ERROR_PROCEDURE();
PRINT N'Error Message = ' + ERROR_MESSAGE();
END CATCH
END
GO
DECLARE @ret int,
@e int;
EXEC @ret = dbo.TestError3 @e OUTPUT;
PRINT N'Error code = ' + CAST(@e AS nvarchar(10));
PRINT N'Return value = ' + CAST(@ret AS nvarchar(10));
The result is similar to Listing 17-2, but SQL Server’s TRY . . . CATCH support gives you more control and flexibility over the output, as shown here:
(0 row(s) affected)
Error Code = 544
Error Procedure = TestError3
Error Message = Cannot insert explicit value for identity column in table
'Address' when IDENTITY_INSERT is set to OFF.
Returned error code = 544
Return value = −6
The T-SQL statements in the BEGIN TRY . . . END TRY block execute normally. If the block completes without error, the T-SQL statements between the BEGIN CATCH . . . END CATCH block are skipped. If an exception is thrown by the statements in the TRY block, control transfers to the statements in the BEGIN CATCH . . . END CATCH block.
The CATCH block exposes several functions for determining exactly what error occurred and where it occurred. We used some of these functions in Listing 17-4 to return additional information about the exception thrown. These functions are available only between the BEGIN CATCH . . . END CATCH keywords, and only during error handling when control has been transferred to it by an exception thrown in a TRY block. If used outside of a CATCH block, all of these functions return NULL. The functions available are listed in Table 17-2.
Table 17-2. CATCH Block Functions
| Function Name | Description |
|---|---|
| ERROR_LINE() | Returns the line number on which the exception occurred. |
| ERROR_MESSAGE() | Returns the complete text of the generated error message. |
| ERROR_PROCEDURE() | Returns the name of the SP or trigger where the error occurred. |
| ERROR_NUMBER() | Returns the number of the error that occurred. |
| ERROR_SEVERITY() | Returns the severity level of the error that occurred. |
| ERROR_STATE() | Returns the state number of the error that occurred. |
TRY . . . CATCH blocks can be nested. You can have TRY . . . CATCH blocks within other TRY blocks or CATCH blocks to handle errors that might be generated within your exception-handling code.
You can also test the state of transactions within a CATCH block by using the XACT_STATE function. We strongly recommend testing your transaction state before issuing a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement in your CATCH block to ensure consistency. Table 17-3 lists the return values for XACT_STATE and how you should handle each in your CATCH block.
Table 17-3. XACT_STATE Function Return Values
| XACT_STATE | Meaning |
|---|---|
| −1 | There is an uncommittable transaction pending. Issue a ROLLBACK TRANSACTION statement. |
| 0 | There is no transaction pending. No action is necessary. |
| 1 | There is a committable transaction pending. Issue a COMMIT TRANSACTION statement. |
The T-SQL TRY . . . CATCH method of error handling has certain limitations attached to it. For one, TRY . . . CATCH can only capture errors that have a severity greater than 10 that do not close the database connection. The following errors are not caught:
- Errors with a severity of 10 or lower (informational messages) are not caught.
- Errors with a severity level of 20 or higher (connection-termination errors) are not caught, because they close the database connection immediately.
- Most compile-time errors, such as syntax errors, are not caught by TRY . . . CATCH, although there are exceptions (e.g., when using dynamic SQL).
- Statement-level recompilation errors, such as object-name resolution errors, are not caught, due to SQL Server’s deferred-name resolution.
Also keep in mind that errors captured by a TRY . . . CATCH block are not returned to the caller. You can, however, use the RAISERROR statement (described in the next section) to return error information to the caller.
TRY_PARSE, TRY_CONVERT, and TRY_CAST
SQL Server 2012 introduces some additional enhancements to the TRY command. The TRY_PARSE, TRY_CONVERT, TRY_CAST functions offer error handling simplicity to some common T-SQL problems. For example the TRY_PARSE function attempts to convert a string value to a date type or numeric type. If the attempt fails then SQL returns a NULL value. In previous versions of SQL Server you would use CAST or CONVERT and need to write code to capture any errors. The syntax for the TRY_PARSE command is as follows:
TRY_PARSE ( string_value AS data_type [ USING culture ] )
The culture statement allows you specifiy the specific language format used for the conversion. This is set regardless of the default SQL Server collation. If no culture is specified then the command will use the default language on the server. Listing 17-5 shows a few examples. The output is shown in Figure 17-1.
Listing 17-5. Examples of TRY_PARSE
DECLARE @fauxdate AS varchar(10)
DECLARE @realdate AS VARCHAR(10)
SET @fauxdate = 'iamnotadate'
SET @realdate = '01/05/2012'
SELECT TRY_PARSE(@fauxdate AS DATE);
SELECT TRY_PARSE(@realdate AS DATE);
SELECT TRY_PARSE(@realdate AS DATE USING 'Fr-FR'),
SELECT IIF(TRY_PARSE(@realdate AS DATE) IS NULL, 'False', 'True')
Figure 17-1. Output of TRY_PARSE Function
The first query attempts to convert a non-date string to a date and fails by returning NULL. The second query succeeds and returns the date 2012-05-01. The third query returns the same date but collates it to the French date format. The final query shows how you can use conditional processing to return any value you want based on whether the conversion succeeds or fails.
The next function is the TRY_CONVERT function. TRY_CONVERT has the same functionality as the CONVERT function but will return a NULL instead of an error if the conversion fails. You use the TRY_CONVERT function when you want to test the possibility of converting one data type to another data type. The syntax is as follows:
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
The data_type is the data type you want to convert the expression into and the style determines formatting. Listing 17-6 shows examples. Figure 17-2 shows each output.
Listing 17-6. TRY_CONVERT Examples
DECLARE @sampletext AS VARCHAR(10)
SET @sampletext = '123456'
SELECT TRY_CONVERT(INT, @ sampletext);
SELECT TRY_CONVERT(DATE, @ sampletext);
SELECT IIF(TRY_CONVERT(binary, @ sampletext) IS NULL, 'FALSE', 'TRUE'),
Figure 17-2. Output of TRY_CONVERT
We first set the variable to a text value which can easily be converted to an integer. The first TRY_CONVERT successfully performs the conversion but the second fails since the text value cannot implicitly be converted to a date. The final example shows that the conversion succeeded with a return result of TRUE.
The final example is the use of TRY_CAST. TRY_CAST is the technical equivalent of TRY_CONVERT but the format is different. The syntax for TRY_CAST is the following:
TRY_CAST ( expression AS data_type [ ( length ) ] )
For demonstration we will use the same examples we used in Listing 17-5 but instead change the syntax to use TRY_CAST. Listing 17-7 shows the differences. The output is the same as in Figure 17-2.
Listing 17-7. Examples Using TRY_CAST
DECLARE @sampletext AS VARCHAR(10)
SET @sampletext = '123456'
SELECT TRY_CAST(@sampletext AS INT);
SELECT TRY_CAST(@sampletext AS DATE);
SELECT IIF(TRY_CAST(@sampletext AS BINARY) IS NULL, 'FALSE', 'TRUE'),
Tip Though useful keep in mind a couple of things about TRY_PARSE, TRY_CONVERT, and TRY_CAST. Parsing strings can be a costly process so use the function sparingly. Microsoft recommends using TRY_PARSE only for converting strings to date or numeric values. For all other conversions use CAST or CONVERT. Also keep in mind that TRY_CONVERT and TRY_CAST will still throw errors for explicit conversions. These are conversions that are not possible. For a chart of implicit and explicit conversions see Books Online (BOL) at http://msdn.microsoft.com/en-us/library/ms191530.aspx.
Throw Statement
SQL Server 2012 introduces the THROW statement. The THROW statement is similar to what we find in programming languages like C++ and C# and can be used instead of RAISERROR. A primary benefit to using the THROW statement instead of RAISERROR is it does not require an error message ID to exist in sys.messages. The THROW statement can occur either inside a CATCH block or outside the TRY . . . CATCH statements. If no parameters are defined then the THROW statement must be within the CATCH block. Listing 17-8 shows examples of both. We’ll use the same INSERT statements as in our previous examples.
Listing 17-8. Examples of the THROW Statement
--1. Using THROW without paramaters
BEGIN TRY
INSERT INTO Person.Address (AddressID)
VALUES (1);
END TRY
BEGIN CATCH
PRINT 'This is an error';
THROW
END CATCH ;
--2. Using THROW with parameters
THROW 52000, 'This is also an error', 1
BEGIN TRY
INSERT INTO Person.Address (AddressID)
VALUES (1);
END TRY
BEGIN CATCH
THROW
END CATCH
(0 row(s) affected)
This is an error
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'Address' when
IDENTITY INSERT is set to OFF.
MSG 52000, Level 16, State 1, Line 1
A couple of things to notice: First off, the only level of severity returned by the THROW statement is 16. The statement doesn’t allow for any other level, which is another difference between THROW and RAISERROR. Another thing to notice is that any statement prior to the THROW statement in the CATCH block must end in a semi-colon. This is yet another reason to make sure all your block statements terminate in semi-colons. If you are already used to using the THROW statement in other programming languages then you should find this a helpful addition to SQL Server 2012.
In procedural languages like C#, debugging code is somewhat easier than in declarative languages like T-SQL. In procedural languages, you can easily follow the flow of a program, setting breakpoints at each atomic step of execution. In declarative languages, however, a single statement can perform dozens or hundreds of steps in the background, most of which you will probably not even be aware of at execution time. The good news is that the SQL Server team did not leave us without tools to debug and troubleshoot T-SQL code. The unpretentious PRINT statement provides a very simple and effective method of debugging.
PRINT Statement Debugging
The PRINT statement, as demonstrated in Listing 17-9, is a simple and useful server-side debugging tool. Simply printing constants and variable values to standard output during script or SP execution often provides enough information to quickly locate problem code. PRINT works from within SPs and batches, but does not work inside of UDFs, because of the built-in restrictions on functions causing side effects. Consider the sample code in Listing 17-9, in which we are trying to achieve an end result where @i is equal to 10. Because the end result of the code is not @ > i = 10, we’ve added a couple of simple PRINT statements to uncover the reason.
Listing 17-9. Debugging Script with PRINT
DECLARE @i int;
PRINT N'Initial value of @i = ' + COALESCE(CAST(@i AS nvarchar(10)), N'NULL'),
SET @i + = 10;
PRINT N'Final value of @i = ' + COALESCE(CAST(@i AS nvarchar(10)), N'NULL'),
The result, shown in Figure 17-3, shows that the desired end result is not occurring because we failed to initialize the variable @i to 0 at the beginning of our script. Because the initial value of @ > i is NULL, our end result is NULL. Once we’ve identified the issue, fixing it is a relatively simple matter in this example.
Figure 17-3. Results of PRINT Statement Debugging
In addition to the simple PRINT statement, you can use the RAISERROR statement with NOWAIT clause to send a message or status indication immediately to the client. While the PRINT statement waits for the buffer to flush, RAISERROR with the NOWAIT clause sends the message immediately.
SQL Server 2012 provides several trace flags that can help with debugging, particularly when you suspect you have a problem with SQL Server settings. Trace flags can turn on or off specific SQL Server behavior, or temporarily change other server characteristics for a server or session. As an example, trace flag 1204 returns the resources and types of locks participating in a deadlock, and the current command affected.
Tip Many trace flags are undocumented, and may only be revealed to you by Microsoft Product Support Services when you report a specific issue; but those that are documented can provide very useful information. BOL provides a complete list of documented SQL Server 2012 trace flags under the title of “Trace Flags.”
Turning on or off a trace flag is as simple as using the DBCC TRACEON and DBCC TRACEOFF statements, as shown in Listing 17-10.
Listing 17-10. Turning Trace Flag 1204 On and Off
DBCC TRACEON (1204, -l);
GO
DBCC TRACEOFF (1204, -l);
GO
Trace flags may report information via standard output, the SQL Server log, or additional log files created just for that specific trace flag. Check BOL for specific information about the methods that specific trace flags report back to you.
SSMS Integrated Debugger
SQL Server 2005 did away with the integrated user interface debugger in SSMS, although it was previously a part of Query Analyzer (QA). Apparently, the thought was that Visual Studio would be the debugging tool of choice for stepping through T-SQL code and setting breakpoints in SPs. Now, in SQL Server 2012, integrated SSMS debugging is back by popular demand. The SSMS main menu contains several debugging actions accessible through the new Debug menu, as shown in Figure 17-4.
Figure 17-4. The SSMS Debug Menu
The options are similar to the options available when debugging Visual Studio projects. From this menu, you can start debugging, step into/over your code one statement at a time, and manage breakpoints. Figure 17-5 shows an SSMS debugging session that has just hit a breakpoint inside the body of a SP.
Figure 17-5. Stepping into Code with the SSMS Debugger
The SSMS debugger provides several windows that provide additional debugging information, including the Call Stack, Breakpoints, Command, Output, Locals, and Watch windows.
Visual Studio 2012 also offers an excellent facility for stepping through SPs and UDFs just like any Visual Basic or C# application. You can access Visual Studio’s T-SQL debugger through the Server Explorer window. Simply expand the data connection pointing at your SQL Server instance and the SP or function you wish to debug under the appropriate database. Then right-click the procedure or function and select Debug Procedure or Debug Function option from the pop-up context menu. Figure 17-6 demonstrates by selecting the Debug Procedure for the dbo.uspGetBillOfMaterials SP in the AdventureWorks 2012 database.
Tip It’s much easier to configure Visual Studio T-SQL debugging on a locally installed instance of SQL Server than to set up remote debugging. BOL offers information about setting up both local and remote SQL Server debugging, in the article “Debugging SQL” (http://msdn.microsoft.com/en-us/library/zefbf0t6.aspx).
Figure 17-6. Debugging the dbo.uspGetBillOfMaterials Procedure
If your function or procedure requires parameters, the Run Stored Procedure window will open and ask you to enter values for the required parameters (see Figure 17-7). For this example, we’ve entered 770 for the @StartProductID parameter and 7/10/2005 for the @CheckDate parameter required by the dbo.uspGetBillOfMaterials procedure.
Figure 17-7. Entering Parameter Values in the Run Stored Procedure Window
After you enter the parameters, the procedure will begin running in Debug mode in Visual Studio. Visual Studio shows the script and highlights each line in yellow as you step through it, as shown in Figure 17-8.
Figure 17-8. Stepping Through an SP in Debug Mode
In Debug mode, you can set breakpoints by clicking the left border and using the Continue (F5), Stop Debugging (Shift + F5), Step Over (F10), Step Into (F11), and Step Out (Shift + F11) commands, just like when you debug C# or Visual Basic programs. You can also add watches and view locals to inspect parameter and variable values as your code executes. Any result sets and return values from the SP are shown in the Visual Studio Output window, as in Figure 17-9.
Figure 17-9. The Visual Studio Output Window
SQL Server MVP Erland Sommarskog said it best: dynamic SQL is a curse and a blessing. Put simply, dynamic SQL is a means of constructing SQL statements as strings in your server-side (or even client-side) applications and executing them dynamically on the fly. When used properly, dynamic SQL can be used to generate complex queries at runtime, in some cases to improve performance, and to do tasks that just aren’t possible (or are extremely difficult) in standard, nondynamic T-SQL.
The downside is that there are numerous ways to shoot yourself in the foot with dynamic SQL. If not used properly, dynamic SQL can open up security holes in your system big enough to drive a truck through. We will discuss the various methods of executing dynamic SQL, as well as some of the risks and rewards that Erland alludes to.
The most basic form of server-side dynamic SQL is achieved by simply passing an SQL query or other instruction as a string to the EXECUTE statement (often abbreviated EXEC). The EXECUTE statement accepts a char, varchar, nchar, or nvarchar constant, variable, or expression that contains valid T-SQL statements. Listing 17-11 shows the most basic form of dynamic SQL with an EXECUTE statement and a string constant.
Listing 17-11. Basic EXECUTE Statement
EXECUTE (N'SELECT ProductID FROM Production.Product'),
As you can see, there is no real advantage to performing dynamic SQL on a string constant. A simple SELECT statement without the EXECUTE would perform the same function and return the same result. The true power of dynamic SQL is that you can build an SQL statement or query dynamically and execute it. Listing 17-12 demonstrates how this can be done.
Listing 17-12. More Complex Dynamic SQL Example
DECLARE @min_product_id int = 500;
DECLARE @sql_stmt nvarchar(128) =
N'SELECT ProductID ' +
N'FROM Production.Product ' +
N'WHERE ProductID > = ' + CAST(@min_product_id AS nvarchar(10));
EXECUTE (@sql_stmt);
Now that we’ve given you this simple code sample, let’s explore all the things that are wrong with it.
In Listing 17-12, the variable @sqlstmt contains the dynamic SQL query. The query is built dynamically by appending the minimum product ID to the WHERE clause. This is not the recommended method of performing this type of query, and we show it here to make a point.
One of the problems with this method is that you lose some of the benefits of cached query plan execution. SQL Server 2012 has some great features that can help in this area, including parameter sniffing and the ability to turn on forced parameterization, but there are many exceptions to SQL Server’s ability to automatically parameterize queries or clauses. To guarantee efficient reuse of cached query execution plans as the text of your query changes, you should parameterize queries yourself.
But the big problem here is SQL injection. Although not really a problem when appending an integer value to the end of a dynamic query (as in Listing 17-12), SQL injection can provide a back door for hackers trying to access or destroy your data when you concatenate strings to create dynamic SQL queries. Take a look at the innocent-looking dynamic SQL query in Listing 17-13. I will discuss how a hacker could wreak havoc with this query after the listing.
Listing 17-13. Basic Dynamic SQL Query with a String Appended
DECLARE @product_name nvarchar(50) = N'Mountain';
DECLARE @sql_stmt NVARCHAR(128) = N'SELECT ProductID, Name ' +
N'FROM Production.Product ' +
N'WHERE Name LIKE ''' +
@product_name + N'%''';
EXECUTE (@sql_stmt);
This query simply returns all product IDs and names of all products that begin with the word Mountain. The problem is with how SQL Server interprets the concatenated string. The EXECUTE statement sees the following concatenated string (the bold portion reflects the value of the variable that was concatenated into the query).
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE 'Mountain%'
A simple substitution for @productname can execute other unwanted statements on your server. This is especially true with data coming from an external source (e.g., from the front end or application layer). Consider the following change to Listing 17-13:
DECLARE @product_name nvarchar(50) =
N'''; DROP TABLE Production.Product; --'
The EXECUTE statement now executes the following string (again, the portion provided by the variable is shown in bold):
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '';
DROP TABLE Production.Product; --%'
The simple dynamic SQL query is now two queries, the second of which will drop the Production.Product table from the database! Now consider if the value of the @productname variable had been retrieved from a user interface, like a web page. A malicious hacker could easily issue arbitrary INSERT, UPDATE, DELETE, DROP TABLE, TRUNCATE TABLE, or other statements to destroy data or open a back door into your system. Depending on how secure your server is, a hacker may be able to use SQL injection to grant him or herself administrator rights, retrieve and modify data stored in your server’s file system, take control of your server, or access network resources.
The only justification for using the string concatenation method with EXECUTE is if you have to dynamically name the tables or columns in your statements. And this is far rarer than many people think. In fact, the only time this is usually necessary is if you need to dynamically generate SQL statements around database, table, or column names—if you are creating a dynamic pivot table-type query or coding an administration tool for SQL Server, for instance.
If you must use string concatenation with the EXECUTE method, be sure to take the following precautions with the strings being passed in from the user interface:
- Don’t ever trust data from the front end. Always validate the data. If you are expecting only the letters A through Z and the numbers 0 through 9, reject all other characters in the input data.
- Disallow apostrophes, semicolons, parentheses, and double hyphens (−−) in the input if possible. These characters have special significance to SQL Server and should be avoided. If you must allow these characters, scrutinize the input thoroughly before using them.
- If you absolutely must allow apostrophes in your data, escape them (double them up) before accepting the input.
- Reject strings that contain binary data, escape sequences, and multiline comment markers (/* and */).
- Validate XML input data against an XML schema when possible.
- Take extra special care when input data contains xp_ or sp_, as it may indicate an attempt to run procedures or XPs on your server.
Tip If you are concatenating one-part table and object names into SQL statements on the server side, you can use the QUOTENAME function to safely quote them. The QUOTENAME function does not work for two-, three-, and four-part names, however.
Usually, data validations like the ones listed previously are performed on the client side, on the front end, in the application layer, or in the middle tiers of multitier systems. In highly secure and critical applications, it may be important to also perform server-side validations or some combination of client- and server-side validations. Triggers and check constraints can perform this type of validation on data before it’s inserted into a table, and you can create UDFs or SPs to perform validations on dynamic SQL before executing it. Listing 17-14 shows a simple UDF that uses the Numbers table created in Chapter 4 to perform basic validation on a string, ensuring that it contains only the letters A through Z, the digits 0 through 9, and the underscore character _, which is a common validation used on usernames, passwords, and other simple data.
Listing 17-14. Simple T-SQL String Validation Function
CREATE FUNCTION dbo.ValidateString (@string nvarchar(4000))
RETURNS int
AS
BEGIN
DECLARE @result int = 0;
WITH Numbers (Num)
AS
(
SELECT 1
UNION ALL
SELECT Num + 1
FROM Numbers
WHERE Num <= LEN(@string)
)
SELECT @result = SUM
(
CASE
WHEN SUBSTRING(@string, n.Num, 1) LIKE N'[A-Z0-9\_]' ESCAPE ''
THEN 0
ELSE 1
END
)
FROM Numbers n
WHERE n.Num <= LEN(@string)
OPTION (MAXRECURSION 0);
RETURN @result;
END
GO
The function in Listing 17-14 uses a CTE to validate each character in the given string. The result is the total number of invalid characters in the string: a value of 0 indicates that all the characters in the string are valid. More complex validations can be performed with the LIKE operator or procedural code to ensure that data is in a prescribed format as well.
Troubleshooting Dynamic SQL
A big disadvantage to using dynamic SQL is in debugging and troubleshooting code. Complex dynamic SQL queries can be difficult to troubleshoot, and very simple syntax or other errors can be hard to locate. Fortunately there is a fairly simple fix for that: write your troublesome query directly in T-SQL, replacing parameters with potential values. Highlight the code, and parse—or execute—it. Any syntax errors will be detected and described by SQL Server immediately. Fix the errors and repeat until all errors have been fixed. Then and only then revert the values back to their parameter names and put the statement back in dynamic SQL. Another handy method of troubleshooting is to print the dynamic SQL statement before executing it. Highlight, copy, and attempt to parse or run it in SSMS. You should be able to quickly and easily locate any problems and fix them as necessary.
One of the restrictions on dynamic SQL is that it cannot be executed in a UDF. This restriction is in place because UDFs cannot produce side effects that change the database. Dynamic SQL offers infinite opportunities to circumvent this restriction, so it is simply not allowed.
The sp_executesql Stored Procedure
The sp_executesql SP provides a second method of executing dynamic SQL. When used correctly it is safer than the simple EXECUTE method for concatenating strings and executing them. Like EXECUTE, sp_executesql takes a string constant or variable as a SQL statement to execute. Unlike EXECUTE, the SQL statement parameter must be an nchar or nvarchar.
The sp_executesql procedure offers a distinct advantage over the EXECUTE method: you can specify your parameters separately from the SQL statement. When you specify the parameters separately instead of concatenating them into one large string, SQL Server passes the parameters to sp_executesql separately. SQL Server then substitutes the values of the parameters in the parameterized SQL statement. Because the parameter values are not concatenated into the SQL statement, sp_executesql protects against SQL injection attacks. sp_executesql parameterization also improves query execution plan cache reuse, which helps with performance.
A limitation to this approach is that you cannot use the parameters in your SQL statement in place of table, column, or other object names. Listing 17-15 shows how to parameterize the previous example.
Listing 17-15. Dynamic SQL sp_executesql Parameterized
DECLARE @product_name NVARCHAR(50) = N'Mountain%';
DECLARE @sql_stmt NVARCHAR(128) = N'SELECT ProductID, Name ' +
N'FROM Production.Product ' +
N'WHERE Name LIKE @name';
EXECUTE sp_executesql @sql_stmt,
N'@name NVARCHAR(50)',
@name = @product_name;
Tip It’s strongly recommended that you use parameterized queries whenever possible when using dynamic SQL. If you can’t parameterize (e.g., you need to dynamically change the table name in a query), be sure to thoroughly validate the incoming data.
Dynamic SQL and Scope
Dynamic SQL executes in its own batch. What this means is that variables and temporary tables created in a dynamic SQL statement or statement batch are not directly available to the calling routine. Consider the example in Listing 17-16.
Listing 17-16. Limited Scope of Dynamic SQL
DECLARE @sql_stmt NVARCHAR(512) = N'CREATE TABLE #Temp_ProductIDs ' +
N'(' +
N' ProductID int NOT NULL PRIMARY KEY' +
N'), ' +
N'INSERT INTO #Temp_ProductIDs (ProductID) ' +
N'SELECT ProductID ' +
N'FROM Production.Product;' ;
EXECUTE (@sql_stmt);
SELECT ProductID
FROM #Temp_ProductIDs;
The #Temp_ProductIDs temporary table is created in a dynamic SQL batch, so it is not available outside of the batch. This causes the following error message to be generated:
(504 row(s) affected)
Msg 208, Level 16, State 0, Line 9
Invalid object name '#Temp_ProductIDs'.
The message (504 row(s) affected) indicates that the temporary table creation and INSERT INTO statement of the dynamic SQL executed properly and without error. The problem is with the SELECT statement after EXECUTE. Since the #Temp_ProductIDs table was created within the scope of the dynamic SQL statement, the temporary table is dropped immediately once the dynamic SQL statement completes. This means that once SQL Server reaches the SELECT statement, the #Temp_ProductIDs table no longer exists. One way to work around this issue is to create the temporary table before the dynamic SQL executes. The dynamic SQL is able to access and update the temporary table created by the caller, as shown in Listing 17-17.
Listing 17-17. Creating a Temp Table Accessible to Dynamic SQL
CREATE TABLE #Temp_ProductIDs
(
ProductID int NOT NULL PRIMARY KEY
);
DECLARE @sql_stmt NVARCHAR(512) = N'INSERT INTO #Temp_ProductIDs (ProductID) ' +
N'SELECT ProductID ' +
N'FROM Production.Product;' ;
EXECUTE (@sql_stmt);
SELECT ProductID
FROM #Temp_ProductIDs;
Table variables and other variables declared by the caller are not accessible to dynamic SQL, however. Variables and table variables have well-defined scope. They are only available to the batch, function, or procedure in which they are created, and are not available to dynamic SQL or other called routines.
Parameterization of dynamic SQL queries is not just a good idea on the server side; it’s also a great idea to parameterize queries instead of building dynamic SQL strings on the client side. Apart from the security implications, query parameterization provides cached query execution plan reuse, making queries more efficient than their concatenated string counterparts. Microsoft .NET languages provide the tools necessary to parameterize queries from the application layer in the System.Data.SqlClient and System.Data namespaces. We discussed parameterization on the client side in Chapter 15.
Summary
SQL Server has long supported simple error handling using the @@error system function to retrieve error information and the RAISERROR statement to throw exceptions. SQL Server 2012 continues to support these methods of handling errors, but also provides modern, structured TRY . . . CATCH and THROW exception handling similar to other modern languages. T-SQL TRY . . . CATCH exception handling includes several functions that expose error-specific information in the CATCH block. SQL Server 2012 also introduced a more streamlined error handling approach to common programming senarios by introducing TRY_PARSE, TRY_CONVERT, and TRY_CAST functions.
In addition to the SSMS integrated debugger, which can be accessed through the Debug menu, SQL Server itself and Visual Studio provide tools that are useful for troubleshooting and debugging your T-SQL code. These include simple tools like the PRINT statement and trace flags, and even more powerful tools like Visual Studio debugging, which lets you set breakpoints, step into code, and use much of the same functionality that is useful in debugging C# and Visual Basic programs.
Also in this chapter, we discussed dynamic SQL, a very useful and powerful tool in its own right, but often incorrectly used. Misuse of dynamic SQL can expose your databases, servers, and other network resources, leaving your IT infrastructure vulnerable to SQL injection attacks. Improper use of dynamic SQL can also impact application performance. SQL injection and query performance are the two most compelling reasons to take extra precautions when using dynamic SQL.
In the next chapter, we will give an overview of SQL Server 2012 query performance tuning.
- [Fill in the blank] The ___________ system function automatically resets to o after every successful statement execution.
- [Choose one] Which of the following functions, available only in the CATCH block in SQL Server, returns the severity level of the error that occurred:
- a. ERR_LEVEL()
- b. EXCEPTION_SEVERITY()
- c. EXCEPTIONALEVEL()
- d. ERROR_SEVERITY()
- [True/False] The RAISERROR statement allows you to raise errors in SQL Server.
- [True/False] Visual Studio provides integrated debugging, which allows you to step into T-SQL functions and SPs and set breakpoints.
- [Choose all that apply] The potential problems with dynamic SQL include which of the following:
- a. Potential performance issues
- b. SQL injection attacks
- c. General exception errors caused by interference with graphics drivers
- d. All of the above