Re: Cursor for loops

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 10/12/04 

Date: Tue, 12 Oct 2004 04:05:04 -0700

> Normally I use the cursor to go through a set of rows and do some actions
> with the values from each row (like calling another procedure, passing a
> column value from that row). How can I achieve the same with the 'set based
> code'?

See the reply I posted in response to Ian. If you *must* invoke a stored
procedure for each row then yes, you have to use a cursor or equivalent
procedural loop. But if that SP just performs straight data manipulation then
you're probably better off rewriting it so that you can perform the operation
once for a whole data set rather than individually for each row. There isn't
a generic answer to your question because it depends on what the SP does. 

-- 
David Portas
SQL Server MVP
--
"Arun" wrote:
> Hi David,
> 
> Can you please let me know how to use the 'set based code' for substituting
> the cursor usage?
> Normally I use the cursor to go through a set of rows and do some actions
> with the values from each row (like calling another procedure, passing a
> column value from that row). How can I achieve the same with the 'set based
> code'?
> 
> Arun
> 
> 
> 
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
> news:E20E1465-2111-491E-856A-ACD237144E94@microsoft.com...
> > SQL Server has a WHILE loop... but cursors, loops and procedural
> processing
> > are best avoided. Try to write set-based code for all your data
> manipulation.
> > Post here if you need help.
> >
> > -- 
> > David Portas
> > SQL Server MVP
> > --
> >
> 
> 
>

Relevant Pages

  • Re: Invalid Cursor - why ?
    ... I am calling a procedure from a remote database, ... TYPE refcur IS REF CURSOR; ... PROCEDURE GetReport (rep OUT refcur, ... I am calling the procedure as follows: ...
    (comp.databases.oracle.server)
  • Re: Invalid Cursor - why ?
    ... I am calling a procedure from a remote database, ... TYPE refcur IS REF CURSOR; ... PROCEDURE GetReport (rep OUT refcur, ... I am calling the procedure as follows: ...
    (comp.databases.oracle.server)
  • Invalid Cursor (corrected)
    ... I am calling a procedure from a remote database, ... TYPE refcur IS REF CURSOR; ... PROCEDURE GetReport (rep OUT refcur, ...
    (comp.databases.oracle.server)
  • Invalid Cursor - why ?
    ... I am calling a procedure from a remote database, ... TYPE refcur IS REF CURSOR; ... PROCEDURE GetReport (rep OUT refcur, ... I am calling the procedure as follows: ...
    (comp.databases.oracle.server)
  • Re: Cursor for loops
    ... > Normally I use the cursor to go through a set of rows and do some actions ... > with the values from each row (like calling another procedure, ... >> SQL Server has a WHILE loop... ... >> David Portas ...
    (microsoft.public.sqlserver.server)