Re: Cursors in functions

anonymous_at_discussions.microsoft.com
Date: 01/22/05 

Date: Sat, 22 Jan 2005 01:29:17 -0800

Hi, this is what I'm after

I got a view like this:
week 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
week:
week 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:
>> 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
>
>.
>

Relevant Pages

  • Re: SQL HELP PLEASE!! Cursor only returns part of the data
    ... First of all, it's not the cursor as such that is bad, it is the looping. ... and re-writing it to handle set-based data will cost you more ... set-based solution do not perform well, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Cursors in functions
    ... > When I want to loop through a result in sp I use Cursor ... Server are extremely slow. ... statements can be slow by itself, since SQL Server has to call the ... function to a single column. ...
    (microsoft.public.sqlserver.programming)
  • Re: Cursor vs. table data type
    ... Cursors are extremely slow. ... Columnist, SQL Server Professional ... Which is more efficient -- a cursor or a table data ...
    (microsoft.public.sqlserver.programming)
  • Re: C5 woes. How stable is VFP really?
    ... After that I close the SQL server cursor and work with my manual cursor instead. ... a VFP9 app on a really old machine ... app was under more "pressure", VFP showed ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Using Cursors
    ... it seems I've stirred up a hornet's nest with my cursor question. ... finding the discussion valuable in my circumstances (moving from Oracle to ... SQL Server). ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)