如下所示:
CREATE FUNCTION [dbo].[getMaterialTable](@PID VARCHAR(32))
RETURNS TABLE
AS
RETURN
(
with CID as
(
select CAST(SMC.ROW_ID AS VARCHAR(1000)) AS PX_CODE,
SMC.*
from SD_MATERIAL_CATEGORY SMC where SMC.ROW_ID=@PID
union all
(select CAST(SMC1.PX_CODE+SMC2.ROW_ID AS VARCHAR(1000)),
SMC2.* from CID SMC1, SD_MATERIAL_CATEGORY SMC2
where SMC1.ROW_ID=SMC2.PARENT_CATEGORY_ID
)
)
SELECT * FROM CID
);
GO