I've seen in several examples where cursors are used particularly on
this board that people use
While @.@.FETCH_STATUS <> -1
BEGIN
IF @.@.FETCH_STATUS <> -2 BEGIN
..
END
END
On most of the Microsoft BOL examples I see that they seem to always
use...
WHILE @.@.FETCH_STATUS = 0
BEGIN
..
END
I have looked up the different meanings
"0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing."
http://msdn.microsoft.com/library/d... />
s_1c1f.asp
To get to the point, my question is why use one method over the other?
Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
might not be apparent?
Do I need to worry about endless loops?
As you might surmise I have a UDF that is running much to long, and
when I look at the number of times my objects have been called, the
number is larger than it should be.Why? Consistency. Almost every fetch loop I've run across looks like this:
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
When you or the poor slob who has to maintain your code looks at it six
months, one, or two years from now, it will be a lot easier to read if it
uses the above pattern.
<Contraptor@.gmail.com> wrote in message
news:1132634990.886050.150040@.g49g2000cwa.googlegroups.com...
> I've seen in several examples where cursors are used particularly on
> this board that people use
> While @.@.FETCH_STATUS <> -1
> BEGIN
> IF @.@.FETCH_STATUS <> -2 BEGIN
> ...
> END
> END
> On most of the Microsoft BOL examples I see that they seem to always
> use...
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> END
> I have looked up the different meanings
> "0 FETCH statement was successful.
> -1 FETCH statement failed or the row was beyond the result set.
> -2 Row fetched is missing."
> http://msdn.microsoft.com/library/d...>
als_1c1f.asp
> To get to the point, my question is why use one method over the other?
> Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
> might not be apparent?
> Do I need to worry about endless loops?
> As you might surmise I have a UDF that is running much to long, and
> when I look at the number of times my objects have been called, the
> number is larger than it should be.
>|||Ok, so al long as I'm not doing anything to change the @.@.FETCH_STATUS
between when I FETCH and check for a valid @.@.FETCH_STATUS I have no
need to worry about infinite loops if using the
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
method.
Now I just need to figure out why my UDF totally iced our test server
last night.
No comments:
Post a Comment