Stored Procedure to build a result set from a tree structure?

Question

I need to write a stored procedure that will take in a string to search a tree like structure and perform a recursive result set. First, here is the table:

CREATE TABLE [dbo].[WorkAreas] (
  [Id] uniqueidentifier DEFAULT newid() NOT NULL,
  [Name] nvarchar(max) COLLATE Latin1_General_CI_AS NULL,
  [ParentWorkAreaId] uniqueidentifier NULL,  
  CONSTRAINT [PK__WorkArea__3214EC073FD07829] PRIMARY KEY CLUSTERED ([Id]),  
  CONSTRAINT [WorkArea_ParentWorkArea] FOREIGN KEY ([ParentWorkAreaId]) 
  REFERENCES [dbo].[WorkAreas] ([Id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION,

)

I'd Like the stored procedure to output the results like this:

Work Area 1 - Child Of Work Area 1 - Child Child Of Work Area 1

So if this were real data it may look like this:

Top Floor - Room 7 - Left Wall
Top Floor - Room 9 - Ceiling

the stored procedure would take in a parameter: @SearchTerm varchar(255)

The search term would look at the results and perform a "contains" query.

So if we passed in "Room 9" the result should come up with the Room 9 example, or if just the word "Room" was passed in, we would see both results.

I am not sure how to construct the SP to recursively build the results.

Have a look at this almost identical question: http://stackoverflow.com/questions/4032192/printing-tree-with-sql-cte — Cade Roux, Oct 17 '11 at 20:34

score 1 · Answer 1 · answered Oct 17 '11 at 21:25

Cade Roux's comment lead me to what I needed. Here is what I ended up with:

;WITH ProjectWorkAreas (EntityId ,ParentIDs,DisplayText) 
AS
(
SELECT  Id,CAST(Id AS VARCHAR(1000)) ,CAST(Name AS VARCHAR(1000))
FROM WorkAreas
WHERE ParentWorkAreaId IS NULL And ProjectId = @projectId

UNION ALL 

SELECT Id, CAST( ParentIDs+','+ CAST(Id AS VARCHAR(100)) 
AS VARCHAR(1000)),CAST( DisplayText + ' - ' + Name AS VARCHAR(1000))     
FROM WorkAreas AS ChildAreas
INNER JOIN ProjectWorkAreas
ON ChildAreas.ParentWorkAreaId = ProjectWorkAreas.EntityId
)



SELECT * FROM ProjectWorkAreas Where DisplayText like '%' + @searchTerm + '%'

I added the ProjectId to the mix

score 0 · Answer 2 · answered Oct 17 '11 at 20:33

0

You would do something like this:

select w1.name, w2.name, w3.name
from workareas w1
inner join workareas w2
on w1.parentworkareaid = w2.id
inner join workareas w3
on w2.parentworkareaid = w3.id
where contains(w3.name, @yourSearchString)

answered Oct 17 '11 at 20:33

That assumes I only have 3 levels, but that is not the case. That was just an example. – DDiVita Oct 17 '11 at 20:38

Stored Procedure to build a result set from a tree structure?

2 Answers2