Create function [dbo].[fun_GetParentStr](@id varchar(10)) RETURNS @t TABLE(bstID varchar(80),bstParentID varchar(100),bstName varchar(100)) as begin WITH cte(bstID,bstParentID,bstName) AS ( SELECT bstID,bstParentID,bstName FROM tb_BuySellType WHERE bstID =@id UNION ALL SELECT fc.bstID,fc.bstParentID,fc.bstName FROM cte INNER JOIN tb_BuySellType AS fc ON cte.bstParentID = fc.bstID ) insert @t SELECT distinct * FROM cte order by bstID return end
