The business purpose of this stored procedure is to permit a project to contain a sub-project. Because each sub-project could again contain other projects, it looked like I could potentiall get infinite loops in my logic. Therefore I wanted to prevent loop backs in my chains of projects.
The recursive stored procedure was able to walk up the chain of containing projects, and could be used to prevent a project from being inserted twice in the same chain.
There were a few tricks involved, and since it’s been about a month since I worked on this, I’m not sure I can remember all that, and for the time being will simply post the create scripts and hope that they are not too implementation specific and perhaps useful to somebody.
There are two important tables – a Projects table, where each project has an integer projectkey, and then a ProjectsOfProjects table, where each entry has a parent key, a child key, and a grandparent key. If you walk up a chain of records, at some point the grandparent key is null, and that is the end of the chain.
/****** Object: StoredProcedure [dbo].[ProjectAncestors] Script Date: 03/11/2009 17:14:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
/*
-- purpose: can we add this child to the specified parent?
-- return false if no, true if yes,
-- returns all the ancestors by recursively calling itself
-- until the last record has a null for the parent key
-- HOW TO RUN:
-- exec ProjectAncestors 40, 30 , 8 , 1
-- Child key - the current project
-- Parent key - the parent project
-- Parent_ikey - the field in the ikey'th record in the ProjectsOfProjects table where the parent's parent is defined
-- (this is the trick that enables us to "walk up" the table and get all the ancestors
SAMPLES: (we had already set up the ProjectsOfProjects table so that there were some children and parents defined)
exec ProjectsOfProjects_ADD 50 , 30, 8 , 1
NOTE - the first execution of [ProjectAncestors] has to have @start=1
declare @OkToInsert bit
exec @OkToInsert = ProjectAncestors 60,30,8,1
print @OkToInsert
exec ProjectAncestors 40, 30 , 8, 1
*/
-- =============================================
CREATE PROCEDURE [dbo].[ProjectAncestors]
@childkey int,
@parentkey int,
@parent_ikey int,
@start int ,
@returncode int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @returncode = -99
print '******* run [ProjectAncestors] NestLevel=' + convert(varchar(10), @@nestlevel)
declare @Kount int
-- 1) if temp table doesn't exist, then create it
if @start = 1
begin
IF OBJECT_ID('tempdb..#ProjectAncestors') IS NOT NULL drop table #ProjectAncestors
-- set up the table to hold the chain of ancestors
CREATE TABLE #ProjectAncestors (parentkey INT)
-- set up the table to hold the global variable
CREATE TABLE #GlobalVars (childkey INT)
INSERT INTO #GlobalVars (childkey ) VALUES (@childkey )
print ' set the original childkey ' + convert(varchar(10), @childkey)
end
-- 2) go ahead and insert the parameter
INSERT INTO #ProjectAncestors (parentkey ) VALUES (@parentkey )
-- 3) check for the end - when the parent_ikey is null, there are no more relationships defined in the ProjectOfProjects table
if @parent_ikey is NULL
begin
-- a. is it valid to make the relationship? if the desired child was already in the list of ancestors, then it is NOT
-- BUT - remember, this is a recursive procedure, the child key is changing each time we call it. We need to check
-- on the original child key
declare @original_childkey int
select @original_childkey=childkey from #GlobalVars
print ' test the @original_childkey=' + convert(varchar(12),@original_childkey)
select @Kount = count(*) from #ProjectAncestors where parentkey=@original_childkey
-- return the result set
select * from #ProjectAncestors
drop table #ProjectAncestors
print '[ProjectAncestors] just before return @Kount=' + convert(varchar(10),@Kount )
if @Kount > 0
begin
print ' return 0 from [ProjectAncestors]'
print ' the input child record ' + convert(varchar(25), @original_childkey)
print ' was found in the list of ancestors, and therefore cannot be added'
set @returncode = 0
print ' @returncode in [ProjectAncestors]:' + convert(varchar(10),@returncode )
IF OBJECT_ID('tempdb..#ReturnCode') IS NOT NULL
begin
insert into #ReturnCode (returncode) values (@ReturnCode)
end
return 0
end
else
begin
print ' return 1 from [ProjectAncestors]'
print ' the input child record ' + convert(varchar(25), @original_childkey)
print ' was NOT found in the list of ancestors, and therefore CAN be added'
set @returncode = 1
print ' @returncode in [ProjectAncestors]:' + convert(varchar(10),@returncode )
IF OBJECT_ID('tempdb..#ReturnCode') IS NOT NULL
begin
insert into #ReturnCode (returncode) values (@ReturnCode)
end
return 1
end
end
else
begin
print '@parent_ikey is NOT NULL ' + convert(varchar(10),@parent_ikey)
declare @new_parentkey int
declare @new_ikey int
select @new_parentkey=parentkey, @new_ikey=parent_ikey from ProjectsOfProjects where childkey=@parentkey and ikey=@parent_ikey
-- **************************************************************
-- RECURSIVE CALL
-- **************************************************************
-- if it is not null, we recurse
exec projectAncestors @parentkey, @new_parentkey, @new_ikey , 0 , @returncode
end
END
And the structure of the ProjectOfProjects table is:
CREATE TABLE [dbo].[ProjectsOfProjects](
[ikey] [int] IDENTITY(1,1) NOT NULL,
[parentkey] [int] NOT NULL,
[childkey] [int] NOT NULL,
[parent_ikey] [int] NULL,
CONSTRAINT [PK_ProjectsOfProjects] PRIMARY KEY CLUSTERED
(
[ikey] ASC
)
