How to convert recursive function into recursive stored procedure

[Rodusa]

I am having problem to apply updates into this function below. I tried
using cursor for updates, etc. but no success. Sql server keeps telling
me that I cannot execute insert or update from inside a function and it
gives me an option that I could write an extended stored procedure, but
I don't have a clue of how to do it. To quickly fix the problem the
only solution left in my case is to convert this recursive function
into one recursive stored procedure. However, I am facing one problem.
How to convert the select command in this piece of code below into an
"execute" by passing parameters and calling the sp recursively again.

### piece of code ############
SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,
@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid

######### my function ###########
CREATE FUNCTION Mkt_GetChildren
(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)
RETURNS decimal
AS
BEGIN

IF EXISTS (SELECT
uid
FROM
categories WHERE
ParentID = @uid)
BEGIN
DECLARE my_cursor CURSOR FOR
SELECT uid, classid5 FROM categories WHERE parentid = @uid

declare @getclassid5 varchar(50), @getuid bigint, @calculate decimal
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @getuid, @getclassid5
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor INTO @getuid, @getclassid5
select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,
@DateEnd)
SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))

END
CLOSE my_cursor
DEALLOCATE my_cursor

SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,
@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid
END
RETURN @subtotal
END
GO

Rod

[Erland Sommarskog]

Rodusa (rc**********@yahoo.com) writes:

I am having problem to apply updates into this function below. I tried
using cursor for updates, etc. but no success. Sql server keeps telling
me that I cannot execute insert or update from inside a function and it
gives me an option that I could write an extended stored procedure, but
I don't have a clue of how to do it.
It would not be a very good idea anyway.
To quickly fix the problem the only solution left in my case is to
convert this recursive function into one recursive stored procedure.
However, I am facing one problem. How to convert the select command in
this piece of code below into an "execute" by passing parameters and
calling the sp recursively again.
The easiest, and most performant may be to use a temp table to
hold the results.

Before I move on to that, let me point out some other problems
in your function:
######### my function ###########
CREATE FUNCTION Mkt_GetChildren
(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)
RETURNS decimal
When you say "decimal" here, this is short for "decimal(18, 0)". Since
you in once place convert to decimal(19, 4), I suspect that this is
not what you. Good practice is any way to always specify both precision
and scale.
DECLARE my_cursor CURSOR FOR
SELECT uid, classid5 FROM categories WHERE parentid = @uid

declare @getclassid5 varchar(50), @getuid bigint, @calculate decimal
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @getuid, @getclassid5
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor INTO @getuid, @getclassid5
select @calculate = dbo.Mkt_CalculateTotal(@getclassid5,
@DateStart,@DateEnd)
SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))
END
CLOSE my_cursor
DEALLOCATE my_cursor

This cursor can be replaced with

SELECT @subtotal = @subtotal +
SUM(dbo.Mkt_CalculateTotal(classid5, @DateStart, @DateEnd)
FROM categories
WHERE parentid = @uid

And say now that you have a table with uid:s to compute for. Then you
would instead have:

SELECT @subtotal = @subtotal +
SUM(dbo.Mkt_CalculateTotal(classid5, @DateStart, @DateEnd)
FROM categories c
JOIN #temp t ON c.parentid = t.uid
WHERE t.currentlevel = @level

Initially you would fill #temp with the @uid passed and currentlevel = 1,
and then you would increase level by 1 each time:

INSERT #temp (uid, level)
SELECT c.parentid, t.currentlevel + 1
FROM categories
JOIN #temp t ON c.parentid = t.uid
WHERE t.currentlevel = @level

After this INSERT you would add:

SELECT @rowc = @@rowcount
IF @rowc = 0 BREAK

As hinted where, you would have a WHILE loop, and not really have any
recursion at all.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rodrigo Lueneberg]

First of all Erland, thank you very much for your help.
Based on your suggestions, I gathered a couple of ideas and I found the
solution to my problem.

I used on temp table to perform the calculation which is, in fact, much
faster than using recursion, just one regular while, like you said.

Then I just modified Mkt_GetChildren below to add up children numbers.

create FUNCTION Mkt_GetChildren
(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)
RETURNS decimal
AS
BEGIN

DECLARE @total decimal

IF EXISTS (SELECT
uid
FROM
categories
WHERE
ParentID = @uid)
BEGIN

SET @subtotal = @subtotal + (SELECT SUM(overall_sales) FROM
categories WHERE ParentID = @uid)

SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal, @DateStart,
@DateEnd)FROM categories WHERE ParentID = @uid
END
RETURN @subtotal
END
GO

With that I've reduce maybe 80% of the processing time.

Thank you very much

Rodrigo Lueneberg

*** Sent via Developersdex http://www.developersdex.com ***

[--CELKO--]

You can avoid all of this procedural code with either:

1) a recursive CTE in a query. You would need to have SQL-2005 or an
SQL product that has the SQL-92 features

2) a switch to a nested sets model for the hierarchy and have no
recursive computation at all. To get a total for a given subtree of any
depth in the nested sets model. For example, the total salaries which
each employee controls in an organization:

SELECT O2.emp, SUM(S1.salary)
FROM OrgChart AS O1, OrgChart AS O2,
Salaries AS S1
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
AND O1.emp = S1.emp
GROUP BY O2.emp;

[Rodusa]

Thanks CELKO,
What do you mean "a switch to a nested sets model"?

Rod

--CELKO-- wrote:

You can avoid all of this procedural code with either:

1) a recursive CTE in a query. You would need to have SQL-2005 or an
SQL product that has the SQL-92 features

2) a switch to a nested sets model for the hierarchy and have no
recursive computation at all. To get a total for a given subtree of any depth in the nested sets model. For example, the total salaries which each employee controls in an organization:

SELECT O2.emp, SUM(S1.salary)
FROM OrgChart AS O1, OrgChart AS O2,
Salaries AS S1
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
AND O1.emp = S1.emp
GROUP BY O2.emp;