שאילתה רקורסיבית

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