Microsoft® SQL Server® 2012 Bible

412

Part IV: Programming with T-SQL

DepartmentID Name

------------ --------------------------

5 Purchasing

6 Research and Development

7 Production

8 Production Control

DepartmentID Name

------------ --------------------------

9 Human Resources

10 Finance

...

One key point to note is that when you page your results in this manner, the entire query

is run each time the WHILE condition is met. Meaning, if you have one million rows and

you fetch fi ve rows at a time, you’ll execute the query 200,000 times. The OFFSET and

FETCH conditions are applied after the full result set has been returned from the server.

This is an abbreviated list of new features in SQL Server 2012. Throughout this book, you’ll see

the application of new features and functionality that you can apply to your environment.

Error Handling

If you’ve never encountered an error in T-SQL, you’re better than most people. For everyone

else, error handling is a critical and often overlooked component of T-SQL programming.

Of course, all robust programming languages provide some method for trapping, logging,

and handling errors. In this area, T-SQL has a sad history, but it’s made signifi cant progress

over the last couple of releases.

There are two distinctly different ways to code error handling with SQL Server:

■ (^) Legacy error handling is how it’s been done since the beginning of SQL Server,
using @@error to see the error status of the previous SQL statement.
■ (^) Try/catch was introduced in SQL Server 2005, bringing SQL Server into the 21st
century. I highly recommend taking this approach.
Legacy Error Handling
Historically, T-SQL error handling has been tedious at best. I’d prefer to not even include
this legacy method of handling errors, but you’ll see it in old code, so it must be covered.
What’s New with Error Handling?
SQL Server 2005 introduced TRY.. .CATCH. SQL Server 2012 rounds out that functionality (and aligns
you with .NET) by introducing THROW. Now, instead of RAISERROR, you have the ability to THROW an
error, which is covered it in the TRY.. .CATCH section.
c16.indd 412c16.indd 412 7/30/2012 5:38:10 PM7/30/2012 5:38:10 PM
http://www.it-ebooks.info