SQL Server如何创建递归函数

摘自《31天学会CRM项目开发<C#编程入门级项目实战>》

本例旨在介绍递归函数的用法，递归函数最大特点在于自己可调用自己。在数据库MyDatabase中有一张名为job的岗位表，其Create脚本如代码片段7-52所示，id是自增字段，parentId指当前岗位的上级岗位id，parentId = 0时表示当前岗位是一级岗位，name指岗位名称。为了显示岗位的层级关系，需获得岗位的完整名称，如图7-37中fullName字段所示。由于岗位的层级数量不确定，所以无法通过if else if语句判断，此时可通过递归函数getjob_fullname(@id int, @fullName varchar(100))，如代码片段7-53所示。

代码片段7-52

CREATE TABLE [dbo].[job](
    [id] [int] NOT NULL,
    [parentId] [int] NOT NULL,
    [name] [nvarchar](50) NULL
) ON [PRIMARY]

片段7-53中行17-19，判断当前记录的parentId是否大于零，如果大于零，表示它有上级岗位，就继续向上查找，否则退出函数。

代码片段7-53

CREATE FUNCTION [dbo].[getjob_fullname]
(
    @id int,
    @fullName varchar(100)
)
returns varchar(100) as 
begin
    declare @s varchar(100);
    declare @parentId int;
    select @s = name, @parentId = parentId from job where id = @id;
    if @s = '' begin
        set @s =@fullName;
    end
    else begin
        set @s = CONCAT(@s,'\',@fullName);
    end;
    if @parentId > 0 begin
        set @s = dbo.getjob_fullname(@parentId, @s);
    end
    return @s;
end;

片段7-54演示了递归函数getjob_fullname(@idint, @fullName varchar(100))的调用方法。值得注意的是在MSSQL中函数递归最大深度是32层，在本例中岗位的层级数量不能超过32。

代码片段7-54

select *,dbo.getjob_fullname(id,'') as fullName from job