Anonymous

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

USE WMS
GO

DROP PROCEDURE GetProjectDrawingChildren
GO

CREATE PROCEDURE GetProjectDrawingChildren (@ProjectId int, @DrawingId int)
AS
	DECLARE	@NumOfChildren int,
			@DrawingCount int,
			@DrawingNum int,
			@ParentId int

	CREATE TABLE #DrawingChildrenData (
		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

				SELECT	DrawingId, DrawingTypeId, DrawingTypeName, ParentDrawingId, ParentRevisionNumber, InternalDrawingNumber, ModuleId
				FROM	#DrawingChildrenData

			END
		ELSE
			BEGIN
				RAISERROR ('Failed to retrieve any drawings for this ProjectId',16,1)
			END
	END
GO