MSDN Magazine - September 2008 - (Page 67)

archyID type. The first query produced a hierarchical listing of the bills from the top (bill 110) down by using a recursive common table expression. When I use the HierarchyID data type, the complete linage for any given bill is already represented within that value. For example, one of the bottommost bills in the hierarchy is bill 405, representing a CPU kit. How would I know what bills make up its lineage? This code will tell me: select billPath.ToString() from dbo.bill2 where billID=405; Determining which bills use a particular part can be queried using the GetAncestor method. That method allows you to query up a hierarchy. Here I need to use recursion to traverse upward through the list of bills: declare @partID int = 20; with c(billPath,descr) as ( select b.billPath,b.descr from dbo.part p join dbo.partList pl on p.partID = pl.partID join dbo.billPartList bpl on pl.partListID = bpl.partListID join dbo.bill2 b on bpl.billID = b.billID where p.partID = @partID union all select b.billPath,b.descr from dbo.bill2 b join c on b.billPath = c.billPath.GetAncestor(1)) select distinct descr,billPath from c where billPath hierarchyID::GetRoot() order by billPath; The output from that query is a string with the value “/110/210/310/405.” Now the question becomes how, exactly, can I get the bills represented by that string? That is a bit trickier since the HierarchyID type does not offer a method for converting the list of values into a vector. However, you can write your own using either TSQL or SQL/CLR. Here is the code for doing this using a TSQL tablevalued function: create function dbo.Vectorize(@i hierarchyID) returns @t table (position int identity(1,1) not null,nodeValue int not null) as begin declare @list varchar(max) = @i.ToString(); declare @delimit int; set @list = substring(@list,2,len(@list)-1); while len(@list) > 1 begin set @delimit = charindex('/',@list); insert into @t values (cast(substring(@list,1,@delimit-1) as int)); set @list = substring(@list,@delimit+1,len(@list)); end; return; end; Figure 6 added a new part and bill. No changes are needed in the code to insert the part or to create the part list. Creating a bill, however, does provide the opportunity to use the GetDescendant and GetReparentedValue methods of HierarchyID. I can start the process with the following code excerpt: begin begin transaction declare @mp int; declare @mpl int; select @mp = MAX(partID)+10 from dbo.part; insert into dbo.part(partID,descr,cost) output inserted.* values (@mp,'4GB USB 2.0 Memory Stick',20.0); select @mpl = MAX(partListID)+10 from dbo.partList; insert into dbo.partList(partListID,partID,quantity) output inserted.* values (@mpl,@mp,1); Now you have a simple way of examining the linage of any bill in the system with a single, nonrecursive query: declare @anyBill int = 405; select b1.billPath,b2.billID,b2.descr from dbo.bill2 b1 cross apply dbo.vectorize(b1.billPath) p join dbo.bill2 b2 on b2.billID = p.nodeValue where b1.billID = @anyBill; What about finding the parts used by a given bill? Unlike the prior implementation where I need to recursively query the list of bills, this query can take advantage of a HierarchyID’s ability to determine whether one ordered path is a descendent of another by using the IsDescendantOf method: declare @anyBill int = 110; declare @sourceBillPath hierarchyID; select @sourceBillPath = billPath from dbo.bill2 where billID = @ anyBill; select p.partID as 'partID', p.descr 'partName' from dbo.bill2 b2 left join dbo.billPartList bpl on b2.billID = bpl.billID left join dbo.partList pl on bpl.partListID = pl.partListID left join dbo.part p on pl.partID =p.partID where b2.billPath.IsDescendantOf(@sourceBillPath)=1 and p.partID is not null order by p.partid; With the part and parts list created, I can now create the related bill. The idea is to insert the new bill into the hierarchy to the right of all of the other bills making up the Super X100 Home Theatre System. To do that I need to know two things: the path for parent bill and the path for its rightmost child. Deleting a bill that is the parent of one or more other bills requires more complex code. Getting the path for the parent bill is a simple query. Finding its rightmost child requires using the IsDescendantOf and GetLevel methods, like this: declare @root hierarchyID; declare @newBillPath hierarchyID; declare @newBillID int; select @root = billPath from dbo.bill2 where billID = 110; select @newBillPath = max(billPath) from dbo.bill2 where billPath.IsDescendantOf(@root) =1 and billPath.GetLevel() = @root.GetLevel()+1; Calculating the MSRP of the whole system or any subbill can be done with a few modifications to the technique that I showed you previously: declare @anyBill int = 110; declare @sourceBillPath hierarchyID; select @sourceBillPath = billPath from dbo.bill2 where billID = @anyBill; select SUM(p.cost * pl.quantity)*2.0 from dbo.bill2 b2 left join dbo.billPartList bpl on b2.billID = bpl.billID left join dbo.partList pl on bpl.partListID = pl.partListID left join dbo.part p on pl.partID =p.partID where b2.billPath.IsDescendantOf(@sourceBillPath)=1; msdnmagazine.com September 2008 67 msdnmagazine.com

Table of Contents Feed for the Digital Edition of MSDN Magazine - September 2008

MSDN Magazine - September 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Cutting Edge Hierarchy ID New Features for Microsoft SQL Server 2008 Prism Data Services Advanced WPF Test Run Security Briefs Foundations { End Bracket }

MSDN Magazine - September 2008

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.