Tip

Three new T-SQL commands in SQL Server 2012 to make your life easier

EDITOR’S NOTE: This is the first of a two-part series about new commands in SQL Server 2012. This article describes changes to stored procedures and error handling. Part 2 delves into more tweaks, including serial numbering.  

Each version of SQL Server has brought incremental changes to Transact-SQL (T-SQL), making programmers’ jobs much easier and rolling in features that the SQL Server user community demands. SQL Server 2012 is no different. In this article, I will examine several new additions to T-SQL that ought to make users a little happier.

For more on SQL Server commands

Find out which SQL command is better for your needs

Get the answers to frequently asked questions about creating and altering SQL Server tables

Learn how to secure SQL Server from SQL injection attacks

WITH RESULT SETS (in EXECUTE)
WITH RESULT SETS addresses a problem that plagues most anyone who has coded a stored procedure as part of their business logic: names for columns tend to be set in stone.

Let’s say you created a stored procedure that returns a few columns of data with specific names and data types. Every time you run that stored procedure, though, you can get only the results with those names and data types. If you change the stored procedure to modify the output, you run the risk of becoming incompatible with the other components -- both inside SQL Server and outside -- that expect the data to return in the same format.

There have been a number of attempts to fix this problem—for instance, creating a parallel stored procedure that returns results in a new format and then gradually migrating all the logic over to the new procedure. Unfortunately, that often means maintaining two stored procedures side by side during the migration period, for however long that lasts.

WITH RESULT SETS is another way to handle the problem, and in my opinion a much more elegant one. It lets you redefine the names and data types of a result set from a stored procedure through the instructions used to call the stored procedure. Here’s an example:

    Requires Free Membership to View

EXEC myStoredProcedure 123

It would normally return an int column named Result_Code. Due to changes in our business logic, we need to have that column named ResultCode. (An applicable situation would be instituting a standard for how columns should be named, and underscores are not permitted). Rather than make changes to the stored procedure itself, we just change where it’s invoked, like so:

EXEC myStoredProcedure 123
WITH RESULT SETS
([ResultCode] int NOT NULL)

Another trick you can use with WITH RESULT SETS is to return multiple result sets:

EXEC myStoredProcedure 123
WITH RESULT SETS
(
([ResultCode] int NOT NULL),([Result_Code] int NOT NULL)
)

This returns two result sets to the client -- the first in the “new” format and the second in the “legacy” format. This way, you can let the client choose which one is appropriate from the same command.

OFFSET and FETCH (in ORDER BY)

Most SQL Server professionals find themselves going out of their way to provide results in a paginated format. There’s been any number of half-hearted solutions to this, from simply caching the entire result set locally on the application side and doing the pagination there to queries using NOT IN or other performance-sapping behaviors.

The new OFFSET command builds in an elegant way, doing pagination entirely on the query side. Let’s say you write this:

SELECT [UserID], [UserName]
FROM [UserTable]
ORDER BY [Username] ASC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

The results are more or less what you’d expect: SQL Server reads from the table, skips the first 10 rows returned by the query and then returns only the next 10 rows. This is a lot less awkward than using nested queries with TOP x-type restrictions.

Note that when you retrieve results using OFFSET/FETCH, the results are considered a single isolated transaction, not a cursor-like object. Let’s say the following happens:

  1. You fetch the first 10 rows from a table using a command similar to the above.
  2. While you’re processing those rows, an INSERT is run against the table to add rows that would show up in the first page of your query.
  3. You fetch the next 10 rows, but those will be the next 10 rows including what was added to the table, not the next 10 rows based on the results from the first query.

Take heed when writing your application and make sure the way your data paginates under live conditions isn’t counterintuitive (or, worse, may create conditions that cause problems with data integrity).

THROW
Error handling in SQL Server is normally handled through the RAISERROR command. RAISERREOR has a few restrictions, though: it can only return an error code defined in sys.messages, although you can use an error number greater than 50,000 to create a custom error. (The default is 50,000, but you can specify an error number). That means it’s most useful for raising system-level errors, rather than errors that relate specifically to your database. The new THROW command allows for error-catching operations that are better suited to T-SQL user applications. Let’s compare the ways it differs from RAISERROR so we can see how they stack up against each other and where each is appropriate.

  1. Most important: RAISERROR always generates a new exception whenever it’s called, so any previously generated exceptions during the routine (for instance, something outside of a CATCH block) are lost. THROW can re-throw the original exception that triggered the CATCH block, so it can provide more detailed context about the error.
  2. RAISERROR is used to generate application- and system-level error codes. THROW generates application-level errors (50,000 or greater) only.
  3. RAISERROR can only pass a custom error message if you use error code 50,000 or greater. THROW lets you pass any error text you want.
  4. RAISERROR supports token substitutions; THROW does not.
  5. RAISERROR supports any severity level of error; THROW only supports error severity 16.

In short, THROW is designed more to be used with T-SQL scripts and stored procedures where you need to return custom errors that are specific to the application you’re creating. Microsoft MVP Leonard Lobel also has a very good dissection of THROW vs. RAISERROR on his blog, with some detailed examples of where each is appropriate.

ABOUT THE AUTHOR
Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeekand Windows Magazine.

This was first published in May 2012

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.