Problem solve Get help with specific problems with your technologies, process and projects.

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

Each release of SQL Server brings changes to Transact SQL. In this tip, expert Serdar Yegulalp gives a walkthrough of tweaks like WITH RESULT SETS for renaming columns and THROW for catching errors.

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.


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:

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
([ResultCode] int NOT NULL)

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

EXEC myStoredProcedure 123
([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.


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

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.

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

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).


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.

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.