May 13, 2009 at 3:02 am
Hi there,
I am trying to write a recursive function that will pull out a bill bill of materials.
The table is very simple really, with no more than about 5 levels, but it keeps going over the 32 level threshold and I'm not sure why.
Here is the function:
[dbo].[GetFullBOM]
(
@ProdNo nvarchar(15)
)
RETURNS
@TempBOM TABLE
(
PARNT nvarchar(15),
CHILD nvarchar(15)
)
AS
BEGIN
WITH BOMTABLE(PARNT, CHILD, ROWINT) AS
(
SELECT PARNT, CHILD, ROW_NUMBER() OVER(ORDER BY PARNT) AS 'ROWINT'
FROM BOM
WHERE PARNT LIKE @ProdNo
)
INSERT INTO @TempBOM
SELECT PARNT, CHILD
FROM BOMTABLE
IF @@ROWCOUNT > 0
DECLARE @RowNo int
SET @RowNo = '0'
WHILE @RowNo <= @@ROWCOUNT
BEGIN
SET @RowNo = @RowNo + 1
DECLARE @CurChild AS nvarchar(15)
SELECT @CurChild = CHILD
FROM BOMTABLE
WHERE ROWINT = @RowNo
BEGIN
INSERT INTO @TempBOM
SELECT *
FROM GetFullBOM(@CurChild)
END
CONTINUE
END
RETURN
END
[/code]
Unfortunately, due to the format of the data (parent and child numbers as strings), I can't use some of the methods I have seen knocking around so am using the ROW_NUMBER() thing.
I am unsure why it seems to exceed the number of nested levels allowed.
Would be massively grateful if someone could cast some light on this!
Thanks
May 13, 2009 at 3:06 am
Put @@ROWCOUNT in a variable, beacause it is always recalculated upon last query.
Hope this helps
Gianluca
-- Gianluca Sartori
May 13, 2009 at 3:09 am
Does this help?
CREATE FUNCTION [dbo].[GetFullBOM] ( @ProdNo nvarchar(15) )
RETURNS @TempBOM TABLE
(
PARNT nvarchar(15),
CHILD nvarchar(15)
)
AS
BEGIN
DECLARE @rowCnt int
WITH BOMTABLE ( PARNT, CHILD, ROWINT )
AS ( SELECT PARNT,
CHILD,
ROW_NUMBER() OVER ( ORDER BY PARNT ) AS 'ROWINT'
FROM BOM
WHERE PARNT LIKE @ProdNo
)
INSERT INTO @TempBOM
SELECT PARNT,
CHILD
FROM BOMTABLE
SET @rowCnt = @@ROWCOUNT
IF @rowCnt > 0
DECLARE @RowNo int
SET @RowNo = '0'
WHILE @RowNo <= @rowcnt
BEGIN
SET @RowNo = @RowNo + 1
DECLARE @CurChild AS nvarchar(15)
SELECT @CurChild = CHILD
FROM BOMTABLE
WHERE ROWINT = @RowNo
INSERT INTO @TempBOM
SELECT *
FROM GetFullBOM(@CurChild)
END
RETURN
END
-- Gianluca Sartori
May 13, 2009 at 3:18 am
Hi Gianluca,
Thanks for your help. I'm afraid it still returns the error message. I have put @@ROWCOUNT into a variable and tried your amended code to no avail.
Thanks
gabe
May 13, 2009 at 7:43 am
Ok, I think this is it:
ALTER FUNCTION [dbo].[GetFullBOM] ( @ProdNo nvarchar(15) )
RETURNS @TempBOM TABLE
(
PARNT nvarchar(15),
CHILD nvarchar(15)
)
AS
BEGIN
WITH BOMTABLE ( PARNT, CHILD, ROWINT )
AS ( SELECT PARNT,
CHILD,
ROW_NUMBER() OVER ( ORDER BY PARNT ) AS 'ROWINT'
FROM BOM
WHERE PARNT LIKE @prodNo
UNION ALL
SELECT O.PARNT, O.CHILD, ROWINT + 1
FROM BOMTABLE AS B
INNER JOIN BOM AS O
ON O.PARNT = B.Child
)
INSERT INTO @TempBOM
SELECT PARNT, CHILD
FROM BOMTABLE
RETURN
END
I put recursion into the CTE instead of the function. I think it will also perform significantly better.
Regards
Gianluca
-- Gianluca Sartori
May 13, 2009 at 8:02 am
Gianluca,
Perfect! Works like a treat! Thanks so much, I am forever in your debt.
Gabe
May 13, 2009 at 8:10 am
You're welcome.
Glad I could help
-- Gianluca Sartori
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply