Recursive Stored Procedure

• k006b

  Valued Member

  Points: 63

  More actions

  March 6, 2002 at 12:43 am

  #78055

  This is something like a referral program

  A webmaster can refer at a max of three downline members under him. I need a query or SP to get the total no of downline members referred by a webmaster. I am using SQL Server7.0 The query or Stored procedure should search entire records for the downline

  members for a webmaster (ie topline webmaster).

  Table Structure:

  CREATE TABLE [usr] (

  [referer_id] [int] NOT NULL ,

  [int] IDENTITY (1, 1) NOT NULL ,

  [name] [varchar] (50) NOT NULL ,

  ) ON [PRIMARY]

  GO

  Some values for you to check:

  insert into usr values(2,'abc1')

  insert into usr values(2,'abc2')

  insert into usr values(2,'abc3')

  insert into usr values(3,'abc4')

  insert into usr values(3,'abc5')

  insert into usr values(3,'abc6')

  insert into usr values(6,'abc7')

  insert into usr values(4,'abc8')

  insert into usr values(7,'abc9')

  insert into usr values(9,'abc10')

  insert into usr values(9,'abc11')

  insert into usr values(10,'abc12')

  insert into usr values(6,'abc13')

  Example 1: total no of downline members referred by NO 2 is 13

  Example 2: total no of downline members referred by NO 6 is 6 (no 6

  referred abc7,abc9,abc10,abc11,abc12,abc13)

  Example 3 : total no of downline members referred by NO 4 is 1 (no 4

  referred abc8)

  Like this it goes...

  Tree structure

  1

  3

  9

  27

  81

  243

  729

  2187

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  March 6, 2002 at 5:32 am

  #424970

  If you know there will be a max of three then I dont think you need recursion, just self join to the table twice with a left join.

  Andy

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter

• Robert W Marda

  SSChampion

  Points: 13413

  More actions

  March 6, 2002 at 5:34 am

  #424972

  Here is another suggestion:

  DECLARE @referer_id int, @downlineID varchar(4), @rowcount int

  CREATE TABLE #Count ([name] varchar(3))

  SET NOCOUNT ON

  SET @referer_id = 6

  SET @rowcount = 1

  WHILE @rowcount <> 0

  BEGIN

  INSERT INTO #Count ([name])

  SELECT RIGHT([name],3)

  FROM usr

  WHERE (referer_id = @referer_id or referer_id IN (SELECT [name] FROM #Count WHERE ISNUMERIC([name]) = 1)

  or referer_id IN (SELECT RIGHT([name],2) FROM #Count WHERE ISNUMERIC(RIGHT([name],2)) = 1 AND ISNUMERIC([name]) = 0)

  or referer_id IN (SELECT RIGHT([name],1) FROM #Count WHERE ISNUMERIC(RIGHT([name],1)) = 1 AND ISNUMERIC(RIGHT([name],2)) = 0))

  AND NOT EXISTS (SELECT [name] FROM #Count WHERE RIGHT(usr.[name],3) = #Count.[name])

  SET @rowcount = @@ROWCOUNT

  END

  --SELECT @rowcount

  SELECT COUNT(*) FROM #Count

  DROP TABLE #Count

  SET NOCOUNT OFF

  Robert Marda

  Robert W. Marda
  Billing and OSS Specialist - SQL Programmer
  MCL Systems