SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONGOUSE WMSGOEXEC GetProjectDrawingChildren 1, 22DROP PROCEDURE GetProjectDrawingChildrenGOCREATE PROCEDURE GetProjectDrawingChildren (@ProjectId int, @DrawingId int)AS DECLARE @NumOfChildren int, @DrawingCount int, @DrawingNum int, @ParentId int CREATE TABLE #DrawingChildrenData ( Id int identity not null, DrawingId int not null, DrawingTypeId int not null, DrawingTypeName varchar(50) not null, ParentDrawingId int, ParentRevisionNumber int, InternalDrawingNumber varchar(50) not null, ModuleId varchar(50) ) BEGIN -- Init Drawing Vars SET @DrawingNum = 1 SET @NumOfChildren = 0 -- Check to see if Project has any drawings IF Exists ( SELECT DrawingId FROM Drawing WHERE ProjectId = @ProjectId ) BEGIN -- Grabs the Number of Drawings in a Project SET @DrawingCount = ( SELECT COUNT (DrawingId) FROM Drawing WHERE ProjectId = @ProjectId ) -- Gets the number of drawings on a project (may be valuable later) WHILE (@DrawingNum < @DrawingCount) BEGIN SET @DrawingNum = (@DrawingNum + 1) END -- Enters the row related to the sp input data into the pseudo table INSERT INTO #DrawingChildrenData (DrawingId, DrawingTypeId, DrawingTypeName, ParentDrawingId, ParentRevisionNumber, InternalDrawingNumber, ModuleId) SELECT D.DrawingId, D.DrawingTypeId, DT.DrawingTypeName, D.ParentDrawingId, D.ParentRevisionNumber, D.InternalDrawingNumber, D.ModuleId FROM Drawing D, DrawingType DT WHERE D.DrawingTypeId = DT.DrawingTypeId AND D.ProjectId = @ProjectId AND D.DrawingId = @DrawingId -- Iterative loop for child drawings WHILE (( SELECT ParentDrawingId FROM Drawing WHERE DrawingId = @DrawingId) IS NOT NULL) BEGIN -- Check to see if drawing selected has parent IF ( (SELECT ParentDrawingId FROM Drawing WHERE DrawingId = @DrawingId) IS NOT NULL ) BEGIN -- Initializes @ParentId SET @ParentId = ( SELECT ParentDrawingId FROM Drawing WHERE DrawingId = @DrawingId ) -- Converts the next iteration of @DrawingId to the previous @ParentId SET @DrawingId = @ParentId SET @NumOfChildren = (@NumOfChildren + 1) -- Adds Parent info to pseudo table INSERT INTO #DrawingChildrenData (DrawingId, DrawingTypeId, DrawingTypeName, ParentDrawingId, ParentRevisionNumber, InternalDrawingNumber, ModuleId) SELECT D.DrawingId, D.DrawingTypeId, DT.DrawingTypeName, D.ParentDrawingId, D.ParentRevisionNumber, D.InternalDrawingNumber, D.ModuleId FROM Drawing D, DrawingType DT WHERE D.DrawingTypeId = DT.DrawingTypeId AND D.ProjectId = @ProjectId AND D.DrawingId = @DrawingId END END -- Table 0 Current Drawing and Parents SELECT DrawingId, DrawingTypeId, DrawingTypeName, ParentDrawingId, ParentRevisionNumber, InternalDrawingNumber, ModuleId FROM #DrawingChildrenData -- Table 1 Children Drawings SELECT DrawingId, DrawingTypeId, DrawingTypeName, ParentDrawingId, ParentRevisionNumber, InternalDrawingNumber, ModuleId FROM Drawing WHERE ParentDrawingId = @DrawingId AND ProjectId = @ProjectId -- You Could Even Get the Welds for the Current Drawing END ELSE BEGIN RAISERROR ('Failed to retrieve any drawings for this ProjectId',16,1) END ENDGO