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 4 9230
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
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 ***
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;
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; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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
|
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))
|
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
|
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:
...
|
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...
|
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,...
|
by: bipinskulkarni |
last post by:
How to write recursive stored procedure in mysql?
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
| |