Cursors in functions

Hi
When I want to loop through a result in sp I use Cursor
and Fetch. But that does not work in functions (atleast
not for me). Anyone that knows what to use instead'
/AnettMay be this helps
According to books online
Cursor operations referencing local cursors that are declared, opened,
closed, and deallocated in the function. Only FETCH statements that assign
values to local variables using the INTO clause are allowed; FETCH
statements that return data to the client are not allowed.
Thanks
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:1afe01c5005c$138a7740$a601280a@.phx.gbl...
> Hi
> When I want to loop through a result in sp I use Cursor
> and Fetch. But that does not work in functions (atleast
> not for me). Anyone that knows what to use instead'
> /Anett|||Anett wrote:
> Hi
> When I want to loop through a result in sp I use Cursor
> and Fetch. But that does not work in functions (atleast
> not for me). Anyone that knows what to use instead'
> /Anett
You should use a set-based approach if at all possible. Cursors in SQL
Server are extremely slow. In fact, using functions on columns in select
statements can be slow by itself, since SQL Server has to call the
function (just like it calls a procedure) for every row in the result
set. And if you can imagine returning a 1,000 rows from a stored
procedure call, you'd really have 1,001 SP calls if you attached a
function to a single column.
If you provide more details about what you are trying to accomplish, we
may be able to offer a set-based solution.
David Gugick
Imceda Software
www.imceda.com|||Hi, this is what I'm after
I got a view like this:
w item used
501 abc 10
502 abc 15
503 abc 5
504 abc 0
505 abc 30
What I want to do is to count the used column for each
w:
w item usedSum
501 abc 10
502 abc 35 (10+15)
503 abc 40 (35+5)
504 abc 40 (40+0)
505 abc 70 (40+30)
Hope you can help me.
thanks

>--Original Message--
>Anett wrote:
>You should use a set-based approach if at all possible.
Cursors in SQL
>Server are extremely slow. In fact, using functions on
columns in select
>statements can be slow by itself, since SQL Server has to
call the
>function (just like it calls a procedure) for every row
in the result
>set. And if you can imagine returning a 1,000 rows from a
stored
>procedure call, you'd really have 1,001 SP calls if you
attached a
>function to a single column.
>If you provide more details about what you are trying to
accomplish, we
>may be able to offer a set-based solution.
>
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Hi, this is what I'm after
> I got a view like this:
> w item used
> 501 abc 10
> 502 abc 15
> 503 abc 5
> 504 abc 0
> 505 abc 30
> What I want to do is to count the used column for each
> w:
> w item usedSum
> 501 abc 10
> 502 abc 35 (10+15)
> 503 abc 40 (35+5)
> 504 abc 40 (40+0)
> 505 abc 70 (40+30)
> Hope you can help me.
> thanks
>
Untested, but it should give you the idea:
SELECT w, item,
(SELECT SUM(used) FROM viewname WHERE w <= o.w
AND item = o.item) AS usedSum
FROM viewname o
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Do you want to see something really scary? mwaahahahahaaa
CREATE FUNCTION dbo.Totaller()
RETURNS int AS
BEGIN
DECLARE @.o int
EXECUTE sp_OACreate 'VBScript.RegExp', @.o OUT
EXECUTE sp_OASetProperty @.o, 'Pattern', ''
RETURN @.o
END
GO
CREATE FUNCTION dbo.Running_Total(@.o int, @.Val int)
RETURNS int AS
BEGIN
DECLARE @.Total int
EXECUTE sp_OAGetProperty @.o, 'Pattern', @.Total OUTPUT
SET @.Total = IsNull(@.Total,0) + IsNull(@.Val,0)
EXECUTE sp_OASetProperty @.o, 'Pattern', @.Total
RETURN @.Total
END
GO
DECLARE @.o int
SET @.o = dbo.Totaller()
SELECT w, item, used, dbo.Running_Total(@.o, used) FROM view
Mr Tea
http://mr-tea.blogspot.com
<anonymous@.discussions.microsoft.com> wrote in message
news:179401c50064$d7ff0070$a401280a@.phx.gbl...
> Hi, this is what I'm after
> I got a view like this:
> w item used
> 501 abc 10
> 502 abc 15
> 503 abc 5
> 504 abc 0
> 505 abc 30
> What I want to do is to count the used column for each
> w:
> w item usedSum
> 501 abc 10
> 502 abc 35 (10+15)
> 503 abc 40 (35+5)
> 504 abc 40 (40+0)
> 505 abc 70 (40+30)
> Hope you can help me.
> thanks
>
> Cursors in SQL
> columns in select
> call the
> in the result
> stored
> attached a
> accomplish, we|||Lee Tudor wrote:
> Do you want to see something really scary? mwaahahahahaaa
> CREATE FUNCTION dbo.Totaller()
> RETURNS int AS
> BEGIN
> DECLARE @.o int
> EXECUTE sp_OACreate 'VBScript.RegExp', @.o OUT
> EXECUTE sp_OASetProperty @.o, 'Pattern', ''
> RETURN @.o
> END
> GO
> CREATE FUNCTION dbo.Running_Total(@.o int, @.Val int)
> RETURNS int AS
> BEGIN
> DECLARE @.Total int
> EXECUTE sp_OAGetProperty @.o, 'Pattern', @.Total OUTPUT
> SET @.Total = IsNull(@.Total,0) + IsNull(@.Val,0)
> EXECUTE sp_OASetProperty @.o, 'Pattern', @.Total
> RETURN @.Total
> END
> GO
> DECLARE @.o int
> SET @.o = dbo.Totaller()
> SELECT w, item, used, dbo.Running_Total(@.o, used) FROM view
> Mr Tea
> http://mr-tea.blogspot.com
>
What's scary is that you posted code without any description of what it
does. And you used COM calls which is scarier.
So what does it do?
David Gugick
Imceda Software
www.imceda.com|||It uses a regexp object (could be anything really, the lighter the better)
to provide persistance of data beween function calls allowing you to add
things like string concatenation, running totals and row numbers (1,2,3,4)
to your select statements.
the example provided gives the requested result and a basis to extend this
to other applications ..

>I got a view like this:
>w item used
>501 abc 10
>502 abc 15
>503 abc 5
>504 abc 0
>505 abc 30

>What I want to do is to count the used column for each w:
>w item usedSum
>501 abc 10 502 abc 35 (10+15)
>503 abc 40 (35+5)
>504 abc 40 (40+0)
>505 abc 70 (40+30)
Mr Tea
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UeajUPAFHA.3528@.tk2msftngp13.phx.gbl...
> Lee Tudor wrote:
> What's scary is that you posted code without any description of what it
> does. And you used COM calls which is scarier.
> So what does it do?
> --
> David Gugick
> Imceda Software
> www.imceda.com