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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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
This was first published in May 2012
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 (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:
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:
- You fetch the first 10 rows from a table using a command similar to the above.
- 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.
- 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.
- 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.
- RAISERROR is used to generate application- and system-level error codes. THROW generates
application-level errors (50,000 or greater) only.
- 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.
- RAISERROR supports token substitutions; THROW does not.
- 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.
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.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation