Take Five with SQL Server 2005

New T-SQL Capabilities in SQL Server 2005 - Part 2 of 3
Kevin S. Goff, Microsoft MVP 2007

In last month's article, I covered the PIVOT, OUTPUT, and TOP N capabilities in T-SQL 2005. This month I’ll cover three more new language features in T-SQL 2005:

• The new APPLY operator that (as the term indicates) allows developers to more efficiently apply/integrate table-valued User-Defined Functions into SQL queries
• New Ranking functions to more easily and effectively assign sequential ranking numbers to a result set
• The new TRY….CATCH error handling capabilities to allow developers and DBAs to write more robust stored procedure code

1 - APPLY

As a database applications developer, I often write table-valued UDFs in SQL Server. If you’re not familiar with table-valued UDFs, they are essentially user-defined functions that perform a query and return the results as a table variable. Typicall you write a table-valued UDF if you have a result set that you want to use across multiple queries (in the same way you’d write any sort of reusable function and call it from multiple programs). For instance, in the AdventureWorks database in SQL Server 2005, if I wanted to return a result set of the TOP N purchase orders for a specific employee, I’d write a UDF to return a table variable:

USE [AdventureWorks]
                GO

/****** Object:  UserDefinedFunction [dbo].[GetTopPurchaseOrders]    Script Date: 03/28/2007 07:18:30 ******/
                IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetTopPurchaseOrders]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
                    DROP FUNCTION [dbo].[GetTopPurchaseOrders]
                set ANSI_NULLS ON
                set QUOTED_IDENTIFIER ON
                go

CREATE FUNCTION [dbo].[GetTopPurchaseOrders]
                       (@EmployeeID INT, @TopN INT)
                RETURNS TABLE
                    AS
                        RETURN
                                SELECT  TOP(@TopN) PurchaseOrderID, EmployeeID, VendorID, OrderDate, TotalDue
                                        FROM Purchasing.PurchaseOrderHeader WHERE EmployeeID = @EmployeeID
                                              ORDER BY TotalDue DESC

To execute this UDF to return the top 10 orders for Employee ID 164, I’d issue the following query:

SELECT   * FROM [dbo].[GetTopPurchaseOrders] (164,10)

So far, I could have done all this in SQL 2000 (except that I’d have to code the TOP N differently – I covered this last month). However, if I want to execute the UDF for every record in the employee database, I’d have to use a temporary table or table variable as part of my query.  In other words, SQL Server 2000 did not allow me to directly APPLY the table-valued UDF against rows in another table as part of a SQL Query. However, SQL Server 2005 introduces the new APPLY operator that allows developers to more efficiently integrate table-valued UDFs:

SELECT Emp.EmployeeID, Emp.LoginID,
                     TopOrders.PurchaseOrderID, TopOrders.VendorID, TopOrders.OrderDate, TopOrders.TotalDue
                                 FROM HumanResources.Employee Emp
                                     CROSS APPLY [dbo].[GetTopPurchaseOrders] (Emp.EmployeeID,5) AS TopOrders
                                         ORDER BY Emp.EmployeeID, TotalDue DESC

The query above reads the Employee table and passes each Employee ID as a parameter to the UDF. The syntax includes the CROSS APPLY statement to apply the UDF against each EmployeeID:

CROSS APPLY [dbo].[GetTopPurchaseOrders] (Emp.EmployeeID,5) AS TopOrders

2 – RANKING

We now want to take the results from the previous query (top N orders by amount due for each employee), and assign a ranking number to each row in the result set. Often, a reporting application will need to display sequential ranking numbers (i.e. #1, #2, #3, etc.)  Prior to SQL Server 2005, a developer either had to write some code to assign a ranking number for each row, or insert the sorted result set into a table that contained an identity key. While doable, this process becomes even more difficult if the result set ranking must be grouped within another entity (e.g. reset the ranking number every time a customer or employee number changes).

Fortunately, T-SQL 2005 contains a new ROW_NUMBER() function that makes this task almost ridiculously simple!!! To start, let’s modify the query from above, to assign an overall ranking number for each of the top 5 sales for each employee:

DECLARE @nTop INT
                  SET @nTop = 5
                 SELECT Emp.EmployeeID, Emp.LoginID,
                                 TopOrders.PurchaseOrderID, TopOrders.VendorID, TopOrders.OrderDate, TopOrders.TotalDue,
                                 ROW_NUMBER() OVER ( ORDER BY TotalDue DESC) AS RankNum
                                 FROM HumanResources.Employee Emp
                                 CROSS APPLY [dbo].[GetTopPurchaseOrders] (Emp.EmployeeID,@nTop) AS TopOrders
                                 ORDER BY RankNum

This will create a result set that ranks the top 5 orders for each employee, overall by Total Amount Due descending. Note the new ROW_NUMBER() OVER function in the SELECT statement, which adds the ranking number to the result set, based on Total Amount Due descending:

ROW_NUMBER() OVER ( ORDER BY TotalDue DESC) AS RankNum

If you want to assign sequential numbers within employee, you can group the ranking by defining a Partition in the statement:

ROW_NUMBER() OVER ( PARTITION BY Emp.EmployeeID ORDER BY TotalDue DESC) AS RankNum
..
ORDER BY Emp.EmployeeID, RankNum

In this case, note that you’ll want to order the result set by the RankNum column within EmployeeID .

Finally, you may face situations where the order is conditional, based on a runtime condition. Suppose you need to assign ranking numbers to a result set where the sort order could be one of several columns.  You can implement a CASE statement:

ROW_NUMBER() OVER (ORDER BY
                     CASE  @SortCol
                        WHEN 'Column1' THEN Column1
                        WHEN 'Column2' THEN Column2
                        ELSE Column1 END)
                    AS RankNum

Note: some developers immediately think of utilizing dynamic SQL when faced with conditional processing (such as the example above). While sometimes it’s hard to completely avoid dynamic SQL, see if you can utilize the CASE statement.

3 – Error Handling with Try…Catch

For years, SQL Server developers have looked for ways to provide robust error-handling in stored procedures, and have coveted the TRY…CATCH capabilities in programming languages like C# and VB.NET.  T-SQL 2005 introduces basic TRY….CATCH capabilities that allow you to more effectively test for errors, and raise errors back to the application layer.

As an example, suppose we try to delete a record from the order header table, based on the Order ID:

DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 44

This line of code will terminate in SQL Server with an error that we’ve attempted to violate a constraint between the Order Header and Order Detail table. If we execute this statement in a stored procedure and call it from a .NET application (or any other application, but I’ll use .NET as the example), the statement will terminate in the database, without reporting anything meaningful back in the application layer.

What we’d like to do is trap that error in the stored procedure, and then raise an error that the database will reporting back to the application layer, which can in turn detect as part of it’s own TRY…CATCH block. Here is the full code for such a stored procedure: we’ll break it down piece by piece.

USE AdventureWorks
 BEGIN
 BEGIN TRANSACTION
          BEGIN TRY
                DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 44
          END TRY
 
          BEGIN CATCH
                DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
                SET @ErrorSeverity = ERROR_SEVERITY()
                SET @ErrorNumber = ERROR_NUMBER()
                SET @ErrorMessage = ERROR_MESSAGE()
                SET @ErrorState = ERROR_STATE()
 
                IF @ErrorState = 0
                       SET @ErrorState = 1
 
                RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,  @ErrorState, @ErrorNumber)
                IF XACT_STATE() < 0
                      ROLLBACK TRANSACTION         
                END CATCH
                COMMIT TRANSACTION
  END
 GO

The very first thing we need to do is wrap the DELETE statement inside a BEGIN TRY…END TRY block. Immediately after that, we code our Begin Catch…End Catch block, which will execute only if the code inside the  BEGIN TRY…END TRY produces some kind of error:

BEGIN TRY
                      DELETE FROM Purchasing.PurchaseOrderHeader WHERE PurchaseOrderID = 44
                END TRY
                BEGIN CATCH
                          -- ereror handling routine
                 END CATCH

Inside the CATCH block, we want to determine the severity level, error number, error message, and error state of the error, so that we can report this information back to the application. Therefore, we create variables and utilize the corresponding SQL functions to retrieve the error information:

 DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT
                 SET @ErrorSeverity = ERROR_SEVERITY()
                 SET @ErrorNumber = ERROR_NUMBER()
                 SET @ErrorMessage = ERROR_MESSAGE()
                 SET @ErrorState = ERROR_STATE()

At this point, we’d like to use the SQL function RAISERROR, which our .NET application will detect in it’s own TRY….CATCH block:

      RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,  @ErrorState, @ErrorNumber)

However, there’s one small fly in the ointment. Constraint violation errors do not report a specific error state code, and RAISERROR requires an error state. So we must check the error state to see if the value is zero, and if so, set it to a value of 1 BEFORE raising the error in SQL Server:

 IF @ErrorState = 0
                                 SET @ErrorState = 1
                 RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,  @ErrorState, @ErrorNumber)

Finally, we can roll back the transaction if SQL Server currently has an active transaction in the session, but the transaction cannot be committed because of the error. We use XACT_STATE() to determine if SQL Server 2005 has classified the transaction as uncommittable:

 IF XACT_STATE() < 0
                            ROLLBACK TRANSACTION

Two other notes on error handling in SQL Server. First (the bad news), T-SQL 2005 does not contain a FINALLY statement that exists in programming languages that implement TRY…CATCH. Second (the good news), developers can nest TRY…CATCH blocks in a stored procedure.

Next month...

Next month, in the conclusion of this three part series on T-SQL 2005 enhancements, we'll look at Common Table Expressions and Recursive Queries, one of the most powerful new capabilities in T-SQL 2005.

Final thoughts:

 For every piece of information that an article covers, there are always multiple items that aren’t covered.  For instance, check out the TOP N WITH TIES option. DENSE_RANK() and NTILE() offer additional ranking capabilities.  Remember…GOOGLE and SQL Server 2005 Books Online are your friends. When you have time, research the bolded keywords in this article - it will lead to more valuable information than you ever thought possible.

About the author:  

Kevin S. Goff has been a Microsoft C# .NET MVP for the last three years, from 2005 through 2007. He is a regular columnist for CoDe Magazine, for the Baker’s Dozen Productivity Tips. He is also the author of Pro VS 2005 Reporting using SQL Server and Crystal Reports, published by Apress. He is the principal consultant for Common Ground Solutions, and specializes in solutions in .NET, SQL Server, and Crystal Reports. His main site is www.commongroundsolutions.net, and his blog is www.TheBakersDozen.net .

Email this Author!

Related Links

• www.commongroundsolutions.net
• www.TheBakersDozen.net
• Kevin's Code Magazine Articles

Add the above content to:

Google   Y! MyWeb   Furl   Simpy   Spurl   Blink  Del.icio.us   Digg   Technorati