Prior to SQL Server 2005, error handling was limited almost exclusively to the @@error system function and the RAISERROR statement, or it was performed through client-side exception handling. T-SQL in SQL Server 2014 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. This chapter discusses legacy T-SQL error-handling functionality and the newer structured error-handling model in T-SQL. The chapter introduces tools useful for debugging server-side code, including T-SQL statements and the Visual Studio IDE.

The chapter also discusses dynamic SQL, which is often more difficult to debug and manage than standard (nondynamic) T-SQL statements. Dynamic SQL, although a useful tool, also has security implications, as you’ll see.

Error Handling

SQL Server 2012 provided several improvements in error handling over SQL Server 2008 and prior releases that have been carried into SQL Server 2014. This section discusses legacy error handling, SQL Server 2008 TRY...CATCH structured error handling, as well as the THROW statement introduced in SQL 2014.

Legacy 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’s automatically reset to 0 after every successful statement. This means you can’t have any statements between the code you think might produce an exception and the code that checks the value of @@error. It also means that after @@error is checked, it’s automatically reset to 0, so you can’t both check the value of @@error and return @@error from in an SP. Listing 18-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 18-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 18-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 in 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 18-2 demonstrates this method of using @@error.

Listing 18-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

RAISERROR 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 18-3 uses RAISERROR to throw an exception with a custom error message, a severity level of 17, and a state of 127.

Listing 18-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 18-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 can’t be 50000. The severity level is a number between 0 and 25, with each level representing the seriousness of the error. Table 18-1 lists the severity levels recognized by SQL Server.

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 in 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 2014 because 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 (for example, batch termination).

RAISERROR can be used in a TRY or CATCH block to generate errors. In the TRY block, if RAISERROR generates an error with a severity between 11 and 19, control passes to the CATCH block. For errors with a severity of 10 or lower, processing continues in the TRY block. For errors with a severity of 20 or higher, the client connection is terminated and control doesn’t pass to the CATCH block. For these high-severity errors, the error is returned to the caller.

Try...Catch Exception Handling

SQL Server 2014 supports the TRY...CATCH model of exception handling, which is common in other modern programming languages and was 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 is invoked only if the statements in the TRY block cause an error. Listing 18-4 demonstrates TRY...CATCH exception handling with a simple SP.

Listing 18-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 18-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. Listing 18-4 uses some of these functions 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 the CATCH block 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 18-2.

TRY...CATCH blocks can be nested. You can have TRY...CATCH blocks in other TRY blocks or CATCH blocks to handle errors that might be generated in your exception-handling code.

You can also test the state of transactions in a CATCH block by using the XACT_STATE function. It’s strongly recommended that you test your transaction state before issuing a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement in your CATCH block, to ensure consistency. Table 18-3 lists the return values for XACT_STATE and how you should handle each in your CATCH block.

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 don’t close the database connection. The following errors aren’t caught:

• Errors with a severity of 10 or lower (informational messages) aren’t caught.
• Errors with a severity of 20 or higher (connection-termination errors) aren’t caught, because they close the database connection immediately.
• Most compile-time errors, such as syntax errors, aren’t caught by TRY...CATCH, although there are exceptions (for example, when using dynamic SQL).
• Statement-level recompilation errors, such as object-name resolution errors, aren’t caught, due to SQL Server’s deferred-name resolution.

Also keep in mind that errors captured by a TRY...CATCH block aren’t 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 introduced additional enhancements to the TRY command. The TRY_PARSE, TRY_CONVERT, and 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, SQL returns a NULL value. In previous versions of SQL Server, you used CAST or CONVERT and had 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 to specify the language format used for the conversion. This is set regardless of the default SQL Server collation. If no culture is specified, the command uses the default language on the server. Listing 18-5 shows a few examples. The output is shown in Figure 18-1.

Listing 18-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')

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 converts 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 TRY_CONVERT. It has the same functionality as the CONVERT function but returns NULL instead of an error if the conversion fails. You can use TRY_CONVERT 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 ] )

data_type is the data type you want to convert the expression into, and style determines formatting. Listing 18-6 shows several examples, and Figure 18-2 shows the output.

Listing 18-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'),

The listing sets 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 because the text value can’t implicitly be converted to a date. The final example shows that the conversion succeeded with a return result of TRUE.

Now let’s look at TRY_CAST.  It’s 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 ) ] )

Listing 18-7 uses the same examples as Listing 18-5 but changes the syntax to use TRY_CAST. The output is the same as in Figure 18-2.

Listing 18-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'),

Throw Statement

SQL Server 2014 introduced the THROW statement.  It’s similar to what you find in programming languages like C++ and C# and can be used instead of RAISERROR. A primary benefit of using THROW instead of RAISERROR is that it doesn’t require an error message ID to exist in sys.messages. The THROW statement can occur either in a CATCH block or outside the TRY...CATCH statements. If no parameters are defined, then THROW must be in the CATCH block. Listing 18-8 shows examples of both. It uses the same INSERT statements as the previous examples.

Listing 18-8. Examples of the THROW Statement

--1. Using THROW without parameters

        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

There are a couple of things to notice: First, the only severity level returned by THROW is 16. The statement doesn’t allow for any other level, which is another difference between THROW and RAISERROR. Also notice that any statement prior to the THROW statement in the CATCH block must end in a semicolon. This is yet another reason to make sure all your block statements terminate in semicolons.

If you’re accustomed to using THROW in other programming languages, you should find this a helpful addition to SQL Server 2014.

Debugging Tools

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 probably aren’t even aware of at execution time. The good news is that the SQL Server team didn’t 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 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 it doesn’t work in UDFs because of the built-in restrictions on functions causing side effects. Consider the example code in Listing 18-9, which tries to achieve an end result where @i is equal to 10. The end result of the code is not @>i = 10, so the listing has a couple of PRINT statements to uncover the reason.

Listing 18-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 18-3, indicates that the desired end result isn’t occurring because I failed to initialize the variable @i to 0 at the beginning of the script. The initial value of @>i is NULL, so the end result is NULL. Once you’ve identified the issue, fixing it is a relatively simple matter in this case.

In addition to the PRINT statement, you can use the RAISERROR statement with a NOWAIT clause to send a message or status indication immediately to the client. Whereas PRINT waits for the buffer to flush, RAISERROR with the NOWAIT clause sends the message immediately.

Trace Flags

SQL Server 2014 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.

Turning on or off a trace flag is as simple as using the DBCC TRACEON and DBCC TRACEOFF statements, as shown in Listing 18-10.

Listing 18-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 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. Integrated SSMS debugging was brought back in SQL Server 2012 and is carried forward in SQL Server 2014. The SSMS main menu contains several debugging actions accessible through the new Debug menu, as shown in Figure 18-4.

The options are similar to those 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 18-5 shows an SSMS debugging session that has just hit a breakpoint in the body of a SP.

The SSMS debugger provides several windows that provide additional debugging information, including the Call Stack, Breakpoints, Command, Output, Locals, and Watch windows.

Visual Studio T-SQL Debugger

Visual Studio 2013 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 Debug menu item. Prior to SQL Server 2014, the debug functionality was available by pointing at your SQL Server instance and the SP or function you wish to debug under the appropriate database. Then you would right-click the procedure or function and select Debug Procedure or Debug Function from the pop-up context menu. Figure 18-6 demonstrates by selecting Debug Procedure for the dbo.uspGetBillOfMaterials SP in the AdventureWorks 2012 database.

SQL Server 2014 debug functionality is now only available via the toolbar or menu item. The right-click debugging functionality was removed from the SQL Server Object Explorer. Figure 18-7 demonstrates the location of the Debug menu and toolbar items.

If your function or procedure requires parameters, right-click the procedure and select Execute Stored Procedure (see Figure 18-8). The Execute Procedure window opens and asks you to enter values for the required parameters (see Figure 18-9). For this example, I entered 770 for the @StartProductID parameter and 7/10/2010 for the @CheckDate parameter required by the dbo.uspGetBillOfMaterials procedure.

After you enter the parameters, the procedure begins running. You must choose to run the procedure in Debug mode to be able to step through the code. Visual Studio shows the script and highlights each line in yellow as you step through it, as shown in Figure 18-10.

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 18-11.

Dynamic SQL

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 security holes in your system that are big enough to drive a truck through. This section discusses the various methods of executing dynamic SQL, as well as some of the risks and rewards that Erland alludes to.

The EXECUTE Statement

The most basic form of server-side dynamic SQL is achieved by passing an SQL query or other instruction as a string to the EXECUTE statement (often abbreviated EXEC). EXECUTE accepts a char, varchar, nchar, or nvarchar constant, variable, or expression that contains valid T-SQL statements. Listing 18-11 shows the most basic form of dynamic SQL with an EXECUTE statement and a string constant.

Listing 18-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 18-12 demonstrates how this can be done.

Listing 18-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 you’ve seen this simple code sample, let’s explore all the things that are wrong with it.

SQL Injection and Dynamic SQL

In Listing 18-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 isn’t the recommended method of performing this type of query, and it’s shown 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 2014 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 18-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 18-13. You see how a hacker could wreak havoc with this query after the listing.

Listing 18-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 returns the 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 result after all the string concatenations are done:

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 (for example, from the front end or application layer). Consider the following change to Listing 18-13:

DECLARE @product_name nvarchar(50) =
    N'''; DROP TABLE Production.Product; --'

As before, the string concatenations result in a statement to be executed. However, this time the statement passed to EXECUTE looks as follows:

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 drops 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, hackers may be able to use SQL injection to grant themselves 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. 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’re 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’re 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) 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_, because it may indicate an attempt to run procedures or XPs on your server.

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 18-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 18-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 18-14 uses a common table expression (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 of 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 are 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 can’t be executed in a UDF. This restriction is in place because UDFs can’t produce side effects that change the database. Dynamic SQL offers infinite opportunities to circumvent this restriction, so it’s simply not allowed.

The sp_executesql Stored Procedure

The sp_executesql SP provides a second method of executing dynamic SQL. When used correctly, it’s safer than the 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 aren’t concatenated into the SQL statement, sp_executesql protects against SQL injection attacks. sp_executesql parameterization also improves reuse of the query execution plan cache, which helps with performance.

A limitation of this approach is that you can’t use the parameters in your SQL statement in place of table, column, or other object names. Listing 18-15 shows how to parameterize the previous example.

Listing 18-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;

Dynamic SQL and Scope

Dynamic SQL executes in its own batch. This means variables and temporary tables created in a dynamic SQL statement or statement batch aren’t directly available to the calling routine. Consider the example in Listing 18-16.

Listing 18-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 isn’t 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. Because the #Temp_ProductIDs table was created in the scope of the dynamic SQL statement, the temporary table is dropped immediately when 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 18-17.

Listing 18-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 aren’t accessible to dynamic SQL, however. Variables and table variables have well-defined scope: they’re only available to the batch, function, or procedure in which they’re created, not to dynamic SQL or other called routines.

Client-Side Parameterization

Parameterization of dynamic SQL queries isn’t 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. In addition to the security implications, query parameterization provides reuse of cached query execution plans, 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. Chapter 16 discussed parameterization on the client side.

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 2014 continues to support these methods of handling errors, but it 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 introduced a more streamlined error-handling approach to common programming scenarios 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 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 when debugging C# and Visual Basic programs.

This chapter also discussed dynamic SQL, a tool that is very useful and powerful in its own right but is 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.

The next chapter gives an overview of SQL Server 2014 query performance tuning.

______________________

¹The Curse and Blessings of Dynamic SQL by Erland Sommarskog; http://www.sommarskog.se/dynamic_sql.html