# Using a Function Recursively

• I would appreciate some feedback. As part of a database conversion project I need to determine the package that has all the options that a customer has selected. The problem is minimized here but basically there is one table that relates options to packages and another table that relates customers to the options they have selected. The challenge is to determine what package has the options the customer has selected.

I am somewhat new at writing T-SQL and would appreciate any feedback on my solution which uses a recursive function. Is there a better or more efficient way? Any comments are welcome.

Here are example tables. I will post my solution next.

```
-- A table that tells what options are in each plan
Create Table    PkgOptions
( Pkg   Integer
, Opt    Char(3)
, CONSTRAINT     PkgOptions_PK   PRIMARY KEY  ( Pkg, Opt  )
)
Go

-- A table that tells what options each customer has selected
Create Table    CustOptions
( CustID   Int
, Opt    Char(3)
, CONSTRAINT    CustOptions_PK   PRIMARY KEY  ( CustID, Opt  )
)
Go

-- Insert some data
Insert into PkgOptions (Pkg, Opt) Values (101,'D01')
Insert into PkgOptions (Pkg, Opt) Values (101,'D02')
Insert into PkgOptions (Pkg, Opt) Values (101,'M01')
Insert into PkgOptions (Pkg, Opt) Values (102,'D01')
Insert into PkgOptions (Pkg, Opt) Values (102,'L01')
Insert into PkgOptions (Pkg, Opt) Values (102,'M01')
Insert into PkgOptions (Pkg, Opt) Values (103,'D01')
Insert into PkgOptions (Pkg, Opt) Values (103,'M02')

Insert into CustOptions (CustID, Opt) Values (1,'D01') -- Pkg 101 or 102
Insert into CustOptions (CustID, Opt) Values (1,'M01')
Insert into CustOptions (CustID, Opt) Values (2,'D01') -- Pkg 102
Insert into CustOptions (CustID, Opt) Values (2,'M01')
Insert into CustOptions (CustID, Opt) Values (2,'L01')
Insert into CustOptions (CustID, Opt) Values (3,'D01') -- Pkg 103
Insert into CustOptions (CustID, Opt) Values (3,'M02')
Insert into CustOptions (CustID, Opt) Values (4,'L01') -- Pkg 102
```
• I created 2 functions. One to prepare the for a call to the main recursive function. Here is the first:

```-- ==================================
-- fn_GetCustPkg is a function that returns one package number
-- that has all the options that the customer has selected.
-- =========================================
CREATE FUNCTION fn_GetCustPkg
(@CustID int)
RETURNS int
AS
BEGIN
DECLARE @Opt  Char(3), @OptList varchar(50)
DECLARE  cur_CustOptions CURSOR LOCAL FOR
SELECT Opt FROM dbo.CustOptions WHERE CustID = @CustID

-- ========================
-- Since we can't pass an array of options create
-- a comma delimited list of options
-- ======================================
SET @OptList = ''
OPEN cur_CustOptions
FETCH NEXT FROM cur_CustOptions INTO @Opt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OptList = @OptList + @Opt + ','
FETCH NEXT FROM cur_CustOptions INTO @Opt
END
-- remove last comma
SET @OptList = SUBSTRING(@OptList,1,LEN(@OptList)-1)

CLOSE cur_CustOptions
DEALLOCATE cur_CustOptions

-- Call fn_GetPkgRecursive to get all packages that satify the options
-- We can only return one package number so choose the lowest valued one
RETURN (SELECT Min(Pkg) FROM dbo.fn_GetPkgRecursive(@OptList))
END
```
• Here is the recursive fuction that does most of work:

```-- ==========================================
-- fn_GetPkgRecursive is a function that recuresively calls
-- itself but ultimately returns package numbers that that have
-- all of the options
-- ==========================================
CREATE FUNCTION fn_GetPkgRecursive
(@OptList varchar(50))
RETURNS @tblPkgsForCust TABLE (Pkg int)
AS
BEGIN
DECLARE
@Opt  char(5),
@OptRemaining varchar(50)
-- ==============
-- Is there more than one option left in the list?
-- ======================================
IF len(ltrim(@OptList))>3
BEGIN
-- ===============
-- There is more than one option in the list.
-- Strip off the first one and pass the rest down to a recursive call
-- to this function.
-- ================================
Set @Opt = SUBSTRING(@OptList, 1, 3)
Set @OptRemaining = SUBSTRING(@OptList, 5, LEN(@OptList)-4)

INSERT @tblPkgsForCust
-- Here is where the recursive call takes place
SELECT Pkg FROM dbo.PkgOptions
WHERE Opt = @Opt
AND Pkg IN (Select * from  fn_GetPkgRecursive(@OptRemaining))
END
ELSE
BEGIN
-- ==================
-- There in only one option in the list.
-- This is the lowest level of recursion.
-- Get all the plans that have this option and pass
-- back to the caller of this function.
-- ==================================
INSERT @tblPkgsForCust
SELECT Pkg FROM dbo.PkgOptions WHERE Opt = @OptList
END
RETURN
END ```

Finally, I can use a SELECT similar to the following to determine the package that satisfies each customer.

```
Select Distinct(CustID), dbo.fn_GetCustPkg(CustID) From CustOptions```

Any feedback would be appreciated. Thanks for your time.

• Hi,

U can get a list of packages that match the customers option by using this sql

SELECT Pkg.Pkg

FROM

(SELECT Pkg As Pkg,

COUNT(*) As OptMatch

FROM PkgOptions

INNER JOIN CustOptions

ON CustOptions.Opt = PkgOptions.Opt AND CustOptions.CustID =4

GROUP BY Pkg) As Pkg

CROSS JOIN

(SELECT COUNT(*) As CustOpt

FROM CustOptions

WHERE CustID = 4) As CustOpt

WHERE Pkg.OptMatch = CustOpt.CustOpt

FOR Just replace the 4 with whatever custid u want

to get the output for each cust . u can modify and use this sql as a derived table and join it with the cust table to get the output as required

Hope this Helps

• You should also be able to use the Shape Provideer and create a shaped recordset to show the relationships.

• GRN, thanks for the suggestion to use COUNT as a way to determine the package. Since I need a query for a data conversion I came up with the following variations.

```
SELECT CustOptCnt.CustID , Min(CustPkgs.Pkg) As Pkg
FROM
(SELECT CustID, COUNT(*) As CustOptCnt
FROM CustOptions
GROUP BY CustID) As CustOptCnt
JOIN
(SELECT CustId, Pkg,  COUNT(*) As OptMatch
FROM PkgOptions INNER JOIN CustOptions
ON CustOptions.Opt = PkgOptions.Opt
GROUP BY CustID, Pkg) As CustPkgs
ON CustOptCnt.CustId = CustPkgs.CustId
AND CustOptCnt.CustOptCnt = CustPkgs.OptMatch
GROUP BY CustOptCnt.CustID
```

also...

```
SELECT  CustId, Min(Pkg) As Pkg FROM
(SELECT CustId,  Pkg
FROM PkgOptions INNER JOIN CustOptions As CustOpts
ON CustOpts.Opt = PkgOptions.Opt
GROUP BY CustID, Pkg
HAVING  COUNT(*) = (SELECT COUNT(*)
FROM CustOptions
WHERE CustID =CustOpts.CustID)
) As MultiCustPkgs
GROUP BY CustID
```

Not sure if there is any difference in performance between the two.