June 29, 2009 at 6:41 pm
I’m trying to replace cursors in stored procedures to retrieve hierarchical data from a table.
The table config is :
CREATE TABLE [dbo].[bs_Business_Units](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Link_ID] [int] NULL,
[Type_ID] [int] NULL,
[Description] [varchar](50) NULL,
[Ord] [int] NULL)
Where Top levels have ID = LinkID, and the tree downwards have parent IDs in the Link ID (Hope this makes sense)
So I used to use two stored Procedures with cursors where I create a temp table, and loop through the structure inserting text into the temp table to generate an accurate XML string with the structure (nodes and childnodes)
But now I’m trying to replace this with CTE queries.
What I have thus far is this query :
With hierarchy (Link_ID,ID, level, Description, Sort,XML) AS
(
Select
Link_ID,
ID,
0,
Description,
cast(Description as varchar(200)) as Sort,
cast('
from bs_Business_Units
where ID = Link_ID
UNION ALL
SELECT
bu.Link_ID,
bu.ID,
level+1,
bu.Description,
cast(sort + '|' + bu.Description as varchar(200)),
cast('
FROM bs_Business_Units bu Inner Join hierarchy ht
on bu.Link_ID = ht.ID
where bu.ID <> bu.Link_ID
)
Select * from hierarchy order by Sort
option (maxrecursion 32767)
The result set from this shows the correct structure in the Sort column but I need to get this into XML to be able to populate a TreeView control accurately .
Link_IDIDLevelDescriptionSortXML
40400CICI
41410FCFC
41421DevFC|Dev
42432TiaanFC|Dev|Tiaan
880Kwagga se businessKwagga se business
8521DeWet van RooyenKwagga se business|DeWet van Rooyen
5245232222Kwagga se business|DeWet van Rooyen|32222
454633333Kwagga se business|DeWet van Rooyen|32222|3333
…..
Can anyone give suggestions/comments on the approach ?
June 29, 2009 at 9:47 pm
I played some more with this and got the necesary data to be able to process the data and get the structure:
With hierarchy (Link_ID,ID,TopLevel,level, Description, Sort,Ord) AS
(
Select
Link_ID,
ID,
ID As Toplevel,
0,
Description,
cast(Description as varchar(2000)) as Sort,
--cast('' as varchar(1000)) as XML
Ord
from bs_Business_Units
where ID = Link_ID
UNION ALL
SELECT
bu.Link_ID,
bu.ID,
ht.TopLevel,
level+1,
bu.Description,
cast(sort + '|' + bu.Description as varchar(2000)),
--cast('' as varchar(1000)) as Sort
bu.Ord
FROM bs_Business_Units bu Inner Join hierarchy ht
on bu.Link_ID = ht.ID
where bu.ID bu.Link_ID
)
Select * from hierarchy order by TopLevel,Sort
option (maxrecursion 32767)
The limitation I found is that you cannot order by the Ord column in the selects so more processing will need to be done for this and at this stage Im thinking the cursors is an older, but better solution.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply