MSDN Magazine - October 2007 - (Page 41)

When writing a custom recursion procedure that does not involve CTEs, you must include an explicit termination clause. The termination clause is responsible for making sure that the recursive algorithm eventually terminates and bubbles back up the recursive call stack. Without this clause, your code could end up in an infinite loop. CTEs, however, have two aspects that help with handling termination clauses. The first is an implicit termination clause that occurs when the recursive member returns zero records. When this happens, the recursive member query does not invoke the CTE recursively but instead bubbles back up the call stack. The other is that you can explicitly set the MAXRECURSION level. The MAXRECURSION level can be set explicitly in a batch that contains the CTE or through a server-side setting (the server-wide setting defaults to 100 unless you change it). This setting limits the number of times a CTE can recursively call itself. If the limit is reached, an exception is thrown. The syntax for setting the MAXRECURSION level is to use the OPTION clause in your SELECT statement that follows the CTE, like so: -- DEFINE YOUR CTE HERE SELECT * FROM EmpCTE OPTION (MAXRECURSION 7) Figure 5 Recursively Gathering Salespeople ;WITH EmpCTE(EmployeeID, EmployeeFirstName, EmployeeLastName, MgrID, SalesLevel) AS ( -- Anchor Member SELECT EmployeeID, FirstName, LastName, ReportsTo, 0 FROM Employees WHERE EmployeeID = 2 -- Start with the VP of Sales UNION ALL -- Recursive Member SELECT e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, m.SalesLevel+1 FROM Employees AS e INNER JOIN EmpCTE m ON e.ReportsTo = m.EmployeeID ) -- Using the CTE SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, MgrID, SalesLevel FROM EmpCTE There are some other rules you should also keep in mind when designing recursive CTEs. A recursive CTE must contain both an anchor member and a recursive member. Both members must have the same number of columns and the columns belonging to both members must have matching datatypes. The recursive member can only refer to the CTE once and the members cannot use the following clauses or keywords: • SELECT DISTINCT • GROUP BY • HAVING • TOP • LEFT/RIGHT OUTER JOIN the employee record for the VP of Sales (EmployeeID = 2). The last column of the anchor member’s query returns a value of 0, which represents the 0th level of the hierarchy, which is the top. The recursive member’s query grabs the list of employees that report to the previous employee. This is accomplished by joining the Employees table to the EmpCTE. The same columns are retrieved from the recursive member, but the SalesLevel column is calculated by taking the current employee’s supervisor, grabbing the supervisor’s SalesLevel, and incrementing it by 1. The expression m.SalesLevel+1 assigns a SalesLevel of 1 to all people who report directly to the VP of Sales (retrieved from our anchor member). Any employees that report to those people then have a SalesLevel of 2. The SalesLevel continues to increment in this way for each subsequent level of the sales organization’s hierarchy. Recursion Excursion In terms of data and rowsets, recursion is used to resolve a problem when you need to perform the same logic repeatedly against the same set of data, under different terms. For example, say you are asked to find all of the salespeople and who they work for and return the data in hierarchical order. Figure 5 demonstrates a solution using a CTE that uses recursion to gather the list of employees that work for the VP of Sales. Except for a few additional aspects, the recursive CTE shown in Figure 5 looks very much like a standard CTE. Where a standard CTE contains a query that defines a rowset, a recursive CTE defines two query definitions. The first query definition, the anchor member, defines a query that will be executed when the CTE is called. The second query definition, the recursive member, defines a query that returns the same columns and datatypes as the anchor member. The recursive member also retrieves the values that will then be used to call back into the CTE recursively. The results of the queries are pulled together using a UNION statement. The EmpCTE in Figure 5 shows an anchor member that grabs Wrapping Up CTEs provide a way to make writing T-SQL much more readable when compared to scenarios that use complex derived tables within a query or referencing a view whose definition is external to the T-SQL batch. CTEs also provide a much improved tool to address the struggles involved in using recursive algorithms. Whether you are using non-recursive or recursive CTEs, you’ll find CTEs can help you address many common development scenarios and improve readability without sacrificing performance. Code download available at msdn.microsoft.com/msdnmag/code07.aspx. Send your questions and comments for John to mmdata@microsoft.com. John Papa is a Senior .NET Consultant with ASPSOFT (aspsoft.com) and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. John, a C# MVP, has authored several books on ADO, XML, and SQL Server. He can often be found speaking at industry conferences, such as VSLive, or blogging at codebetter.com/ blogs/john.papa. Data Points october2007 41 http://msdn.microsoft.com/msdnmag/code07.aspx http://aspsoft.com http://codebetter.com/blogs/john.papa http://codebetter.com/blogs/john.papa

Table of Contents Feed for the Digital Edition of MSDN Magazine - October 2007

Cover Contents Toolbox CLR Inside Out Basic Instincts Data Points Cutting Edge Pooled Threads WPF Threads Parallel Linq Parallel Performance Mobile Apps Test Run Foundations Windows with C++ Netting C++ .NET Matters { End Bracket } Net Nuptials

MSDN Magazine - October 2007

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.