SQL递归获取当前类别及所有子类别,用于按类型查询

SQL Script:

ALTER FUNCTION [dbo].[ufn_GetSupplierCategory]
(
@CategoryCode VARCHAR(20) --类别编号
)
RETURNS @RET TABLE (CategoryCode VARCHAR(20)) --返回表结构
AS
BEGIN
/*******************************************************************

功能：递归获取当前类别及所有子类别,用于按类型查询

SELECT * FROM dbo.dt_SupplierCategory
SELECT * FROM dbo.ufn_GetSupplierCategory('000')
SELECT * FROM dbo.ufn_GetSupplierCategory('002')
SELECT * FROM dbo.ufn_GetSupplierCategory('00000')

********************************************************************/

--当前类别编号
INSERT INTO @RET VALUES(@CategoryCode)

DECLARE @Tmp VARCHAR(30)
DECLARE #CUR CURSOR FOR SELECT CategoryCode FROM dt_SupplierCategory WHERE CategoryParent=@CategoryCode

OPEN #CUR
FETCH NEXT FROM #CUR INTO @Tmp
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @RET SELECT CategoryCode FROM dbo.ufn_GetProductCategory(@Tmp)
FETCH NEXT FROM #CUR INTO @Tmp
END
CLOSE #CUR
DEALLOCATE #CUR

RETURN

END

//来源:C/S框架网(www.csframework.com) QQ:23404761