Cursor for loops

Is there something in T-SQL that is the same as FOR loops in Oracle PL/SQL?
if not, what is the closest equivalent?
i don't know Oracle however have a look at ...
WHILE @.i < 100
BEGIN
-- your task
SET @.i = @.i + 1
END
i hope it helps
ian
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:Oy%23fjvDsEHA.516@.TK2MSFTNGP09.phx.gbl...
> Is there something in T-SQL that is the same as FOR loops in Oracle
> PL/SQL?
> if not, what is the closest equivalent?
>
>
|||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
|||No, no, I didn't mean simply 'for loops', but 'cursor for loops'.
They look like this in Oracle:
FOR mycursor IN (SELECT PERSON_ID, NAME FROM T_PERSON)
LOOP
doSomething(mycursor.PERSON_ID);
END LOOP;
I think this is a very useful construct, therefore I'm looking for something
similar in T-SQL.
"Ian Oldbury" <ian_oldbury@._NO_msn_SPAM_.com> wrote in message
news:Orj1$wDsEHA.2136@.TK2MSFTNGP14.phx.gbl...
> i don't know Oracle however have a look at ...
> WHILE @.i < 100
> BEGIN
> -- your task
> SET @.i = @.i + 1
> END
>
> i hope it helps
> ian
> "Agoston Bejo" <gusz1@.freemail.hu> wrote in message
> news:Oy%23fjvDsEHA.516@.TK2MSFTNGP09.phx.gbl...
>
|||That sort of loop is normally only useful if the operation represented by
"doSomething" is something inherently procedural, like sending an email or
invoking some other external process. For those occassions you can use a
WHILE loop with a cursor (better still, do that process at the client or
middle-tier). See Books Online for examples of cursors.
For data manipulation you should avoid using cursors and loops.
David Portas
SQL Server MVP
|||a cursor does have its uses however most things can be done by avioding
them, as they're a heafty resource.
DECLARE
cReOrder CURSOR LOCAL FOR
SELECT @.ID, @.DisplayOrderFROM
<table>
WHERE <whatever>
OPEN cReOrder
FETCH cReOrder INTO @.ID, @.DisplayOrder
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- INSERT OR WHATEVER
FETCH NEXT FROM cReOrder INTO @.ID, @.DisplayOrder
END
CLOSE cReOrder
DEALLOCATE cReOrder
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:ejO$IHEsEHA.536@.TK2MSFTNGP11.phx.gbl...
> No, no, I didn't mean simply 'for loops', but 'cursor for loops'.
> They look like this in Oracle:
> FOR mycursor IN (SELECT PERSON_ID, NAME FROM T_PERSON)
> LOOP
> doSomething(mycursor.PERSON_ID);
> END LOOP;
> I think this is a very useful construct, therefore I'm looking for
> something
> similar in T-SQL.
>
> "Ian Oldbury" <ian_oldbury@._NO_msn_SPAM_.com> wrote in message
> news:Orj1$wDsEHA.2136@.TK2MSFTNGP14.phx.gbl...
>
|||There are customers in SQL Server. Check out DECLARE. (Not the DECLARE that you declare variables,
but the one with which you declare a cursor.) Then you combine that with a while loop. There are
examples in Books Online.
As already mentioned, most problems can be solved with set based logic (typically a single SELECT,
INSERT, UPDATE or DELETE) and that is most often much much faster than looping using a cursor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message news:ejO$IHEsEHA.536@.TK2MSFTNGP11.phx.gbl...
> No, no, I didn't mean simply 'for loops', but 'cursor for loops'.
> They look like this in Oracle:
> FOR mycursor IN (SELECT PERSON_ID, NAME FROM T_PERSON)
> LOOP
> doSomething(mycursor.PERSON_ID);
> END LOOP;
> I think this is a very useful construct, therefore I'm looking for something
> similar in T-SQL.
>
> "Ian Oldbury" <ian_oldbury@._NO_msn_SPAM_.com> wrote in message
> news:Orj1$wDsEHA.2136@.TK2MSFTNGP14.phx.gbl...
>
|||Hi David,
I've been trying to find out that point at which cursor or blah blah are
actually acceptable to use.
At what point is it better to use the cursor instead of the other available
options. What tips have you got...?
cheers for your thoughts
ian
"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
> --
>
|||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
> --
>
|||I'd estimate that for most people using SQL Server at least 99.99% of data
manipulation can be done without a cursor. Inevitably there are inherently
procedural tasks that are not regular data manipulation and these are what I
would call acceptable uses of a cursor. I'm talking about things like sending
emails and automated management processes such as monitoring databases,
updating statistics, rebuilding indexes, managing backups, etc.
Sometimes operational constraints (as distinct from technical ones) may
compel you to use a cursor where you might be better off without. For example
some existing code in a stored procedure that needs to be invoked for each
row of a set and where you haven't the remit or the resources to rewrite that
SP.
There is also a relatively small class of tasks which don't have feasible
set-based solutions but where a cursor proves more efficient. These are rare
in my experience although one or two examples have been discussed in this
group and elsewhere. If you think you have encountered one of these then
always get a second opinion. Post the problem here if you like.
Finally there is what anecdotal evidence suggests is by far the most common
use of a cursor: those written by developers familiar with procedural
languages who don't know SQL well enough to write effective set-based code.
There are lots of examples of these. Unfortunately this code is usually
inefficient, doesn't scale well and is often very costly to support and
maintain. As a guide, I haven't written a single cursor for an actual
data-manipulation task in the last 5 years. So if you are writing curors
(except for admin-type tasks) regularly then you should certainly think again
about whether your code is as efficient, reliable and maintainable as it
ought to be.
David Portas
SQL Server MVP