Solved

Recursive selection of table content

Posted on 2010-11-28
9
249 Views
Last Modified: 2012-05-10
I'm trying to move my understanding of recursion to tsql.

id     name                subid
1      Steve              3
2      Margot              3
3      Family              6
4      Other Family      6
5      Evert              4
6      Department      7
7      City                 8
8      State              9
9      Country      

I understand that sql allow 32 levels of recursives deep.
I have to approaches with this test:

1 Make a recursive stored procedure that gives a result with all related subid ordered

2. Make it like a path (ponder if there was a web path that needs to be concated)


And also that @@nestLevel is a sort of built in function for avoiding deeper levels.
0
Comment
Question by:dingir
  • 4
  • 4
9 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34225534
Can you please explain your question? Maybe Common Table Expression (CTE) is what you are looking for.
0
 
LVL 1

Author Comment

by:dingir
ID: 34225569
What is cte?

I would like to get i.e.
Sp(2) and got the concated path all recursived.
Steve, family, department, city and so on..

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34225987
> Sp(2) and got the concated path all recursived.

Just one record? The entire path?
Have a look at this example
-- create a test table

create table testcte(id int,name varchar(20),subid int)

insert testcte select

1,'Steve',3 union all select

2,'Margot',3 union all select

3,'Family',6 union all select

4,'Other Family',6 union all select

5,'Evert',4 union all select

6,'Department',7 union all select

7,'City',8 union all select

8,'State',9 union all select

9,'Country', null

GO



-- create the test procedure

create proc usp_testcte @start int as

;with cte as (

-- the part before "union all" is called the anchor, it starts the recursion

	select subid, path=convert(varchar(max),name) from testcte where id=@start

	union all



-- the part after "union all" is called the recursive part. In here, "cte" references

-- the result from the most recent iteration of the recursion (starts with the anchor)

-- as long as this query generates results, it will continue to run, using the

-- previous iteration as the "table" content of "cte" ("cte" being the name given to the

-- common table expresion as the first word after "with")

	select t.subid, cte.path+'\'+t.name from cte

	inner join testcte t on t.id=cte.subid)



-- outside the CTE block, you can access the built-up results by referring to "cte" as a table

-- MAX is used here to just the last row, which in this case will be the longest

select max(path) from cte

GO



-- test it

exec usp_testcte 1

Open in new window

0
 
LVL 1

Author Comment

by:dingir
ID: 34226046
Hm.. i feel this isnt the fully right way..?

Yes one record as result (2 given as parameter, the id of the table).
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34226328
> Hm.. i feel this isnt the fully right way..?

And why would you say that?
0
 
LVL 1

Author Comment

by:dingir
ID: 34226559
Why would i need so much code to that task?
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34226571
Have you read the code at all?
The first part sets up a table so that I can show you how it works in the 2nd part (the procedure you need).
The 3rd part is a sample of how you would use such a procedure.
If all you are after is a fast cut and paste answer, this would be all there is to create such a proc

create proc usp_name_me @start int as
;with cte as (
      select subid, path=convert(varchar(max),name) from testcte where id=@start
      union all
      select t.subid, cte.path+'\'+t.name from cte
      inner join testcte t on t.id=cte.subid)
select max(path) from cte
GO


Is that better?  Replace all bold parts with your actual table/field names
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34226574
All the lines starting with 2 dashes "--" are comments to help you understand the code, but it seems you may not be interested.
0
 
LVL 1

Author Comment

by:dingir
ID: 34341377
I care, absolutely.. i wasn't expect such a solution though. I will store it and see if it came of use later on. Thank's a lot and sorry for the big delay.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now