Recursive display path of SQL2000 Functions

----------------------------------------------------------------------------------
-- Author: htl258 (Tony)
-- Date: 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
----------------------------------------------------------------------------------
--> Generate a test data table: [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
--> The SQL query is as follows:

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

(One row is affected)
*/