SQL2000 函数递归显示路径

来源：互联网发布：中国11月外汇储备数据编辑：程序博客网时间：2024/05/30 07:14

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2011-05-19 17:25:59
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--          Jul 9 2008 14:43:34
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [Tree]
IF OBJECT_ID('[Tree]') IS NOT NULL
    DROP TABLE [Tree]
GO
CREATE TABLE [Tree] ([ID] [int],[Text] [nvarchar](10),[PID] [int])
INSERT INTO [Tree]
SELECT '1','A',NULL UNION ALL
SELECT '2','B','1' UNION ALL
SELECT '3','C','2' UNION ALL
SELECT '4','D','3'

--SELECT * FROM [Tree]
GO
-->SQL查询如下:

IF OBJECT_ID('getdpt')>0
    DROP FUNCTION getdpt
GO
CREATE FUNCTION GetDPT(@Id INT)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @dptn NVARCHAR(50), @dpti NVARCHAR(20)
    SELECT @dptn = [Text], @dpti = PID
    FROM Tree
    WHERE ID = @Id
    RETURN
        CASE
             WHEN ISNULL(@dpti, '')='' THEN @dptn
             ELSE ISNULL(dbo.GetDPT(@dpti)+'->', '')+@dptn
        END
END
GO

SELECT dbo.GetDPT(4)
/*
A->B->C->D

(1 行受影响)
*/