Recursive selection of table content

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you please explain your question? Maybe Common Table Expression (CTE) is what you are looking for.
dingirAuthor Commented:
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..

> 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

-- 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+'\' from cte
	inner join testcte t on

-- 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

-- test it
exec usp_testcte 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dingirAuthor Commented:
Hm.. i feel this isnt the fully right way..?

Yes one record as result (2 given as parameter, the id of the table).
> Hm.. i feel this isnt the fully right way..?

And why would you say that?
dingirAuthor Commented:
Why would i need so much code to that task?
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+'\' from cte
      inner join testcte t on
select max(path) from cte

Is that better?  Replace all bold parts with your actual table/field names
All the lines starting with 2 dashes "--" are comments to help you understand the code, but it seems you may not be interested.
dingirAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.