USE [Temp]
GO
/****** Object: StoredProcedure [dbo].[cte_parts] Script Date: 07/20/2011 13:30:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Igor Margulyan
-- exec cte_parts '01-0001-01'
-- =============================================
ALTER PROCEDURE [dbo].[cte_parts]
@item as varchar(10)
AS
Begin
SET NOCOUNT ON;
with CTE (main_part, sub_part, NestingLevel)
as
(
select main_part, sub_part, 1 from tblParts
where main_part = @item
union all
select tblParts.main_part, tblParts.sub_part, (NestingLevel + 1) fromtblParts
inner join CTE on tblParts.main_part = CTE.sub_part
)
select * from CTE
End