473,287 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

How to convert recursive function into recursive stored procedure

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

Jul 23 '05 #1
4 9227
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
Jul 23 '05 #2
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 ***
Jul 23 '05 #3
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;

Jul 23 '05 #4
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;


Jul 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: William Buchanan | last post by:
Hi I have the following stored proc in interbase (which might contain errors - i'm doing it off the top of my head), which I would like to convert into oracle. Can you help? What I want back is...
2
by: Mich | last post by:
I'm going crazy trying to convert an Access Function to SQL. From what I've read, it has to be done as a stored procedure. I'm trying to take a field that is "minutes.seconds" and convert it to...
3
by: Robert Song | last post by:
Hi all I am implementing a stored procedure which needs to recursively call itself until specific condition is reached, Could anyone give some advice about that? Thanks a lot Robert Song
5
by: simon | last post by:
I have datetime variable: Datetime tsEndTime; Should I use (DateTime): tsEndTime=(DateTime)rdr.GetValue(15) or is better to use: tsEndTime=Convert.ToDateTime(rdr.GetValue(15))
4
by: Henrik Juul | last post by:
How do I call my Stored Procedure recursively: CREATE PROCEDURE dbo.GetParentIONode ( @IONodeID int, @FullNodeAddress char(100) OUTPUT ) AS BEGIN
3
by: oregondba | last post by:
I have a database I have inherited (new job). I am trying to baseline the code and have done a DB2Look to get the code out of an existing db. The DB2Look produced a SP with the following form: ...
0
by: champ1979 | last post by:
I wrote an algorithm to get all the relatives of a person in a family tree. I'm basically getting all the users from the DB and am doing the recursive logic in code, so that there is only 1 call...
11
by: lenygold via DBMonster.com | last post by:
I am tryieng to convert our time consuming recursive queries too very efficient queries based on nested set model. The only problem is to convert an adjacency list model into a nested set model,...
1
by: bipinskulkarni | last post by:
How to write recursive stored procedure in mysql?
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.