Why not simply WHILE @@FETCH_STATUS = 0?

[Andrew J. Kelly]

It really depends on what type of cursor you use. If you use a static
cursor then you can never get anything other than a 0 since all the rows
will always be there. If you have a dynamic cursor you could attempt to
fetch a row that someone just deleted and it will fail. If your only
checking for 0 it will fail and you would most likely stop fetching assuming
they are all done. Where as if you checked for more than 0 you can be more
flexible.

--

Andrew J. Kelly
SQL Server MVP


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:ut4$ByyRDHA.2460@TK2MSFTNGP10.phx.gbl...

> SQL 6.5 BOL recommended a two-step process of making sure FETCH NEXT
> returned results:
> FETCH NEXT FROM ...
> WHILE (@@fetch_status <> -1)
> BEGIN
> IF (@@fetch_status <> -2)
> etc.
>
> The reason it gave never made sense to me: "Because @@fetch_status will
> return -2, -1, or 0, all three cases must be tested"
>
> The SQL2000 BOL examples more simply say:
>
> WHILE @@fetch_status = 0
> BEGIN
> etc.
>
> However, the cursor templates in QA revert to the two-step process. I'm
> confused: why is the 2-step process considered de rigeuer?
>
> Bob Barrows
>
>
>

[Bob Barrows]

Ah, that makes sense. Many thanks.

Bob

Andrew J. Kelly wrote:

> It really depends on what type of cursor you use. If you use a static
> cursor then you can never get anything other than a 0 since all the
> rows will always be there. If you have a dynamic cursor you could
> attempt to fetch a row that someone just deleted and it will fail.
> If your only checking for 0 it will fail and you would most likely
> stop fetching assuming they are all done. Where as if you checked
> for more than 0 you can be more flexible.
>
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:ut4$ByyRDHA.2460@TK2MSFTNGP10.phx.gbl...

>> SQL 6.5 BOL recommended a two-step process of making sure FETCH NEXT
>> returned results:
>> FETCH NEXT FROM ...
>> WHILE (@@fetch_status <> -1)
>> BEGIN
>> IF (@@fetch_status <> -2)
>> etc.
>>
>> The reason it gave never made sense to me: "Because @@fetch_status
>> will return -2, -1, or 0, all three cases must be tested"
>>
>> The SQL2000 BOL examples more simply say:
>>
>> WHILE @@fetch_status = 0
>> BEGIN
>> etc.
>>
>> However, the cursor templates in QA revert to the two-step process.
>> I'm confused: why is the 2-step process considered de rigeuer?
>>
>> Bob Barrows

[abdheshtech]

Why we use "@@Fetch_status"