Function acum(a , &b) ????? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Function acum(a , &b) ?????

Author

Topic

nfsoft
Starting Member

36 Posts

Posted - 2004-07-05 : 12:51:28

Looks like TSQL doesn't accept variable parameter.

I whas trying to create this function...

CREATE FUNCTION ACUM(nValue float, oValue float)
RETURNS float
AS
begin
select oValue = oValue + nValue
return (@oValue)
end

to be used like this...

declare @auxv float
select @auxv = 0.0

CREATE VIEW extract AS
SELECT name, value, dbo.acum(value, @auxv)
FROM sales

THIS simply doesn't work becouse @auxv is not updated inside the function.

Can someone help. tanks

Nuno Ferreira

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-05 : 13:48:53

Are you trying to create a running sum??

You are correct that it doesn't update @auxv in the function as @variables are local.
You would have set the @auxv equal to the return value of the function. This doesn't work as you are in a query.

Is there a particular order to your query?? Maybe this will help:

Select name, value, aCum = (Select sum(value) from sales Where name <= A.name)
From sales as A
Order By Name

Corey

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 04:33:54

these means I need to have enumerated column so I can do the sum.
No can do.
If only I cold create a global variable or something, I could make this function work.
Looks like functions in TSQL are very poor. You can't do anything with tables execpt select. why?

Nuno Ferreira

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 04:34:46

Sory... I forgot to say tanks. Tanks Corey.

Nuno Ferreira

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 04:57:33

Here is an article on using non-deterministic function to sum. You probably don't want to actually do it like this, but it's an interesting concept.

[url]http://weblogs.sqlteam.com/jeffs/articles/1490.aspx[/url]

I made one to traverse a tree. Corey, you might find it interesting. Search for UDF

[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21047219.html[/url]

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 06:03:55

that looks grate... what I was looking for.
Hope that performens does't afect the result.
Tanks a lot... I will come back and post the result so others can save the troble.
Tanks again.

Nuno Ferreira

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 06:05:30

Really you don't want to do that! It is more of a technology demonstration.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 06:18:43

I guess I should say more about why you shouldn't do it. sp_oacreate can only be run by a system administrator level user. It runs in the same memory space as SQL but is not managed very well. You can potentially crash the server, corrupt memory therby corrupting data pointers or who knows what. (The vbscript.regexp is probably less likley to do that than a custom written component, but is is still possible) It is not particularly fast, and datatype checking is not robust. All invocations of sp_oacreate share a single OLE Automation manager and one client can lock up or corrupt all users.

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 09:01:24

That mean the solution is safer using cursors to get the resolt I want?
I have tested it and looks ok... but using se it runs in the same memory space, I will never do something like that.
TSQL should provide a global var declaration.

But as I sayd here is the code I was looking for...
-----------------------------------------------------------
CREATE FUNCTION dbo.iniAcum()
RETURNS Integer AS
BEGIN
DECLARE @obj integer
EXECUTE sp_OACreate 'VBScript.RegExp', @obj OUTPUT
EXECUTE sp_OASetProperty @obj, 'Pattern', ''
RETURN @obj
END

CREATE FUNCTION dbo.runAcum(@obj Integer, @newValue float)
RETURNS float AS
BEGIN
DECLARE @value sql_variant
DECLARE @result float
DECLARE @tmp varchar(50)
EXECUTE sp_OAGetProperty @obj, 'Pattern', @value OUTPUT
SET @result = convert(float,isnull(@value,0.0)) + @newValue
SET @value = convert(sql_variant,@result)
EXECUTE sp_OASetProperty @obj, 'Pattern', @value
RETURN (@result)
END

CREATE FUNCTION dbo.freeAcum(@obj integer)
RETURNS float AS
BEGIN
EXEC sp_OADestroy @obj
RETURN (null)
END

declare @a int;
set @a = dbo.iniAcum();
select 1.4, ects, dbo.runAcum(@a,1.4) union all
select 1.2, ects, dbo.runAcum(@a,1.2) union all
select 0.3, ects, dbo.runAcum(@a,0.3)
SET @a = dbo.freeAcum(@a)
--------------------------------------------------------
but, as kselvia sayd, the risk is to high to be used. But it breaks my hart letting this
code skip my hands.

well, back to zero!...

What shold I do?

Nuno Ferreira

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-06 : 09:29:58

Question: why are you calculating the running total? it is to display it on a report or a web page, or are you using the value calculated furhter in your SQL processing?

if you are just calculting the value to display, do this at the presentation layer. in ASP or a reporting tool (like Crystal, Access, or SQL Rpt services) it is trivial to implement.

I would recommend that you try to calculate things like running totals and line numbers and all that ONLY if you need this running total for further SQL processing (i.e., taking this resultset and using it in other queries or stored procs).

- Jeff

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 11:37:35

No you don't need a cursor. The solution suggested by seventhnight should work fine.

Select name, value, aCum = (Select sum(value) from sales Where name <= A.name)
From sales as A
Order By Name

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-06 : 11:43:49

I was expecting this to be easy in SQL Server 2005, but it's not really, unless they've implemented more of the ANSI SQL 99 OLAP stuff than they're telling us and given it funny names.

Doing row numbers is easy since you've got ROW_NUMBER(), but for a running total, you need an accumulator. That points toward using recursive CTEs. And surprisingly, it works ok with a table that's got (effectively) a row number as a primary key. Although it only appears to be able to use a loop join on the recursive part, so it's probably running in O(n log n) rather than O(n). Even so, it polished off 100000 rows in fairly short order.
Using ROW_NUMBER() to join the bits together doesn't work very well: it eventually made it on a 10000 row table, but it was pausing every 200 rows of output for some reason.
I might come back with a worked example if I can be bothered.

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 12:10:34

The revelation

I am working, developing for the University a scrip to create a set o classes (subjects) that will build a form to be printed, showed on the web, used by front desk operators and students (very interactive), and finally it will be validated (run time) by any of the interfaces that use the data. Etch subject has a ECTS (European Credit Transtation System) -- a valeu -- and a student has a quote of ects. By using a sumary column on the sum of ects I would gain some independance of the interface implementation.
But the hard work will be done on the Server when it geneters all the subjects that students may chouse.
(Sory but my englih is very bad... my portugues is not better).
That is my context.
Tanks to all... I will abandone this... Time is running out.
Loved to chat... tanks again. (specialy to kselvia)

Nuno Ferreira

nfsoft
Starting Member

36 Posts

Posted - 2004-07-06 : 12:54:28

tanks all again I am a very slow reader I didn't see the last posts.
Arnold Fribble I try to do something like that but didn't work.
Looks like if you use a aux table you can use a identity and that helps a lot, sinsce kselvia last post shows the correct way to do the job. But I don't have a ordered set, and I was avoiding Stored Procedures, Temp tables and spetialy cursors. Only a view!
Is so sad that the function kselvia sogested is not safe, becouse it was the beste thing I came to until today.
(haaaa... Break time... See you tomorow).
tanks again. you all.

Nuno Ferreira

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources