473,856 Members | 1,713 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_GetChil dren(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_Calcula teTotal(@getcla ssid5, @DateStart,
@DateEnd)
SET @subtotal = CONVERT (decimal (19,4),(@subtot al + @calculate))

END
CLOSE my_cursor
DEALLOCATE my_cursor

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

Rod

Jul 23 '05 #1
4 9278
Rodusa (rc**********@y ahoo.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_Calcula teTotal(@getcla ssid5,
@DateStart,@Dat eEnd)
SET @subtotal = CONVERT (decimal (19,4),(@subtot al + @calculate))
END
CLOSE my_cursor
DEALLOCATE my_cursor


This cursor can be replaced with

SELECT @subtotal = @subtotal +
SUM(dbo.Mkt_Cal culateTotal(cla ssid5, @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_Cal culateTotal(cla ssid5, @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****@sommarsk og.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_sal es) FROM
categories WHERE ParentID = @uid)

SELECT @subtotal = dbo.Mkt_GetChil dren(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
5962
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 a dataset of all the records where Ch_Val is different to the previous value. Thanks for any help. create procedure GetChanges(StartDate timestamp, EndDate timestamp)
2
5025
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 minutes. This is what I have in Access: Function ConvertToTime (myAnswer As Variant) Dim myMinutes myMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4)))) ConvertToTime =(myMinutes)
3
6167
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
18734
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
12979
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
3199
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: Create SP1 (various code) call sp1()
0
1964
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 made to the DB. However, I am trying to do the same thing within a stored procedure in SQL using recursive CTEs (I think the performance might be better) but I'm finding it really tough to craft the CTE. I would really appreciate if someone could...
11
2861
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, with push down stack algorithm to DB2 query. The client does not want to use Stored Procedure. Please any Recurcive or CWE ideas. Thank's in advance. -- Tree holds the adjacency model
1
3907
by: bipinskulkarni | last post by:
How to write recursive stored procedure in mysql?
0
9903
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9755
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11048
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10692
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10770
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10376
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7928
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7084
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5754
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

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.