How does SQL Server recursively query hierarchical data Merge parent fields with a field of this level? How do I customize user functions and invoke them?

To come to the point, first of all, the problem encountered: the pre-system Area dictionary table, each province and county only saved the name of this level, the Complete field is not stored. such as: Feixi County under the Anhui province Hefei, the table has saved a Feixi County. The existing requirements need to display the full field, because the system has been operating online, unable to make too many complex changes, the initial plan is to add a new field in the table, the field is filled, one is convenient to modify, the second is for later if other functions can be used to take the new field, simplifying the work.

OK, the problem is clear, then think about how to solve the problem. is not a more rare problem, just idle bored, open the garden to write essays, may be able to help people with related issues.

Around two questions.

How does SQL Server recursively query hierarchical data Merge parent fields with a field of this level?

We all know that recursion in Oracle is implemented via connect by prior, how does SQL Server work? There is no such keyword assistance in SQL Server.

The regional table structure is as follows:

SQL Server recursive code sticker:

------Query tree structure a node's ancestor all nodes

With Areadata (Sid,ssuperid,sname)
As
(

---starting condition
Select M.sid,m.ssuperid,m.sname
from [Adoptionregister_membership]. [dbo]. [Mdb_area] m where sid= ' 340824 '--Lists child node query criteria

--Recursive conditions
UNION ALL
Select A.sid,a.ssuperid,a.sname
from [Adoptionregister_membership]. [dbo]. [Mdb_area] A
INNER JOIN
Areadata B on A.sid=b.ssuperid

--Query parent information based on child node parent field
)

SELECT * FROM Areadata

The query results are as follows:

Now we're going to consider how to combine the fields, which we can do with stuff, with the following code:

Select Stuff ((
Select ' +sname
From Areadata where sid!= ' 000000 ' order is sID ASC FOR XML Path (') '), 1,0, ') as name; -----sid!= ' 000000 ' here is filtering the whole country this line of data

The query results are as follows:

OK, the above has initially resolved how to recursively query hierarchical data merge the parent field with a field of this level! Now we continue to look at the second question.

How do I customize user functions and invoke them?

is to create a custom scalar-valued custom function that encapsulates the recursive query we just wrote and returns the merged value, which is a simple place. The code is as follows:

Use [Adoptionregister_manage]
GO
/****** object:userdefinedfunction [dbo]. [Get_area_fullname] Script date:01/10/2018 15:50:55 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo]. [Get_area_fullname]
(
@sid nvarchar (40)

)
RETURNS nvarchar (40)

As

BEGIN

DECLARE @str nvarchar (40)

Set @str =n ' not found ';

With Areadata (Sid,ssuperid,sname)
As
(
Select M.sid,m.ssuperid,m.sname
from [Adoptionregister_membership]. [dbo]. [Mdb_area] m where [email protected]
UNION ALL
Select A.sid,a.ssuperid,a.sname
from [Adoptionregister_membership]. [dbo]. [Mdb_area] A
INNER JOIN
Areadata B on A.sid=b.ssuperid
)
Select @str =stuff ((
Select ' +sname
From Areadata where sid!= ' 000000 ' ORDER by sID ASC FOR XML Path (') '), 1, 0, ')

RETURN @str

END

------------------------------

Test call

SELECT dbo. Get_area_fullname (' 340824 ')

-----------

At this point we have solved the problem of throwing, and finally use the function to update the new field in the table is OK!

Update [adoptionregister_membership]. [dbo]. [Mdb_area] Set sfullname=dbo. Get_area_fullname (SID)

Look at the final result:

It's over! Hope to help you!

How does SQL Server recursively query hierarchical data Merge parent fields with a field of this level? How do I customize user functions and invoke them?