Resetting @@Fetch_Status

Hi:
if I run a loop like so...
While @.@.FETCH_STATUS = 0
BEGIN
..Some code
END
It will break out of loop when @.@.FETCH_STATUS = -1. The problem is if I try
to run it again, @.@.FETCH_STATUS will remain at -1 and loop in never entered.
I have to close connection to get it to reset. How do I force it to reset
so I can work with it repeatedly in Query Analyzer?
Thanks,
CharlieCharlie@.CBFC wrote:
> Hi:
> if I run a loop like so...
> While @.@.FETCH_STATUS = 0
> BEGIN
> ...Some code
> END
>
> It will break out of loop when @.@.FETCH_STATUS = -1. The problem is
> if I try to run it again, @.@.FETCH_STATUS will remain at -1 and loop
> in never entered. I have to close connection to get it to reset. How
> do I force it to reset so I can work with it repeatedly in Query
> Analyzer?
>
You need to perform a FETCH that does not result in a non-zero status.
Of course, some will say that you should stop using cursors ... ;-)
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On Mon, 14 Nov 2005 16:26:26 -0500, Charlie@.CBFC wrote:

>Hi:
>if I run a loop like so...
>While @.@.FETCH_STATUS = 0
> BEGIN
> ...Some code
> END
>
>It will break out of loop when @.@.FETCH_STATUS = -1. The problem is if I tr
y
>to run it again, @.@.FETCH_STATUS will remain at -1 and loop in never entered
.
>I have to close connection to get it to reset. How do I force it to reset
>so I can work with it repeatedly in Query Analyzer?
Hi Charlie,
The generic outline (in pseudo-code) for consuming a trigger is:
-- Declare and open the curor
-- Fetch first row
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Process data from one row
-- Fetch next row
END -- repeats until FETCH_STATUS <> 0
-- Close and deallocate the cursor
Of course, some will say that you should stop using cursors ... ;-)
(and I would agree!!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> The generic outline (in pseudo-code) for consuming a trigger is:
Consuming what? Haven't your mummy told you not to be up that late
at night? Or least stop consuming? :-)

> -- Declare and open the curor
> -- Fetch first row
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- Process data from one row
> -- Fetch next row
> END -- repeats until FETCH_STATUS <> 0
> -- Close and deallocate the cursor
Nah, this is the way should it, in my opinion:
DECLARE cur INSENSITIVE CURSOR FOR
SELECT ...
SELECT @.err = @.@.error IF @.err <> 0 BEGIN DEALLOCATE cur RETURN END
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO ...
IF @.@.fetch_status <> 0
BREAK
-- Do stuff
END
DEALLOCATE cur
The point is that with only one FETCH statement, and which is close
to the cursor declaration, you don't run the risk of adding one more
column to the cursor, updating the first FETCH, but forgetting the
second.
And the cursor should be INSENSITIVE to avoid the nasty pitfalls of
keyset-driven cursors (which I still have not understood). FAST_FORWARD
or STATIC are probably OK to, but INSENSITIVE is ANSI.

> Of course, some will say that you should stop using cursors ... ;-)
> (and I would agree!!!)
Me too!
Then again, cursors are perfectly acceptable in some situations. And if
you have decided that you will iterate - a cursor is the best choice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm curious ... I use cursors all the time. Why would I want to stop using c
ursor? And
what would I use to replace the cursor functionality?
On Mon, 14 Nov 2005 23:40:08 +0000 (UTC), Erland Sommarskog <esquel@.sommarsk
og.se> wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>Consuming what? Haven't your mummy told you not to be up that late
>at night? Or least stop consuming? :-)
>
>Nah, this is the way should it, in my opinion:
> DECLARE cur INSENSITIVE CURSOR FOR
> SELECT ...
> SELECT @.err = @.@.error IF @.err <> 0 BEGIN DEALLOCATE cur RETURN END
> OPEN cur
> WHILE 1 = 1
> BEGIN
> FETCH cur INTO ...
> IF @.@.fetch_status <> 0
> BREAK
> -- Do stuff
> END
> DEALLOCATE cur
>The point is that with only one FETCH statement, and which is close
>to the cursor declaration, you don't run the risk of adding one more
>column to the cursor, updating the first FETCH, but forgetting the
>second.
>And the cursor should be INSENSITIVE to avoid the nasty pitfalls of
>keyset-driven cursors (which I still have not understood). FAST_FORWARD
>or STATIC are probably OK to, but INSENSITIVE is ANSI.
>
>Me too!
>Then again, cursors are perfectly acceptable in some situations. And if
>you have decided that you will iterate - a cursor is the best choice.|||Gaetan <someone@.somewhere.com> wrote:

>I'm curious ... I use cursors all the time. Why would I want to stop using
cursor? And
>what would I use to replace the cursor functionality?
Cursors allow row-at-a-time processing. SQL Server is not written for
row-at-a-time processing, and so cursors are, in general, inefficient.
SQL works best when used relationally, everything-at-once rather than
row-at-a-time. Cursors are invaluable, but should be considered a
last resort.
Row-at-a-time cursors look familiar to programmers who are used to
conventional programming languages, and often cursors are used by such
programmers as the only apparent (to them) way to get things done.
They are used as a first approach, rather than a last resort - a
substitute for relational thinking.
Often we find that a problem which has been solved by a cursor could
have been solved relationally, without a cursor. It is not uncommon
for the relational approach to outperform the cursor significantly -
we can be talking about an order of magnitude.
All of which is a broad generalization, and of course there are
exceptions. But like many such generalizations, the exceptions are
far fewer than some would have us believe.
Roy|||I understand what you are saying but I fail to understand how else I could u
se a
relational processing to perform a row-at-a-time process. For example, in a
SP I need to
apply some business logic on rows of a table matching specific criterias and
return the
output;
Table XYZ -> CURSOR -> Business processing on each row -> insert into temp t
able -> select
* from temp table
How could this type of processing be performed without the use of a CURSOR?
If I could get
an order of magnitude performance gain, you bet that I will change a few sto
red procedures
right away.
Thanks.
On Mon, 14 Nov 2005 22:43:17 -0500, Roy Harvey <roy_harvey@.snet.net> wrote:

>Gaetan <someone@.somewhere.com> wrote:
>
>Cursors allow row-at-a-time processing. SQL Server is not written for
>row-at-a-time processing, and so cursors are, in general, inefficient.
>SQL works best when used relationally, everything-at-once rather than
>row-at-a-time. Cursors are invaluable, but should be considered a
>last resort.
>Row-at-a-time cursors look familiar to programmers who are used to
>conventional programming languages, and often cursors are used by such
>programmers as the only apparent (to them) way to get things done.
>They are used as a first approach, rather than a last resort - a
>substitute for relational thinking.
>Often we find that a problem which has been solved by a cursor could
>have been solved relationally, without a cursor. It is not uncommon
>for the relational approach to outperform the cursor significantly -
>we can be talking about an order of magnitude.
>All of which is a broad generalization, and of course there are
>exceptions. But like many such generalizations, the exceptions are
>far fewer than some would have us believe.
>Roy|||Well, what is the business processing you are attempting to do? Is it
not something that can be applied to all of the rows at once?
Stu|||Gaetan (someone@.somewhere.com) writes:
> I understand what you are saying but I fail to understand how else I
> could use a relational processing to perform a row-at-a-time process.
> For example, in a SP I need to apply some business logic on rows of a
> table matching specific criterias and return the output;
> Table XYZ -> CURSOR -> Business processing on each row -> insert into
> temp table -> select * from temp table
> How could this type of processing be performed without the use of a
> CURSOR? If I could get an order of magnitude performance gain, you bet
> that I will change a few stored procedures right away.
That depends on the business processing to apply. Admittedly, in some
cases you can only work with it one at time, but say that the business
processing is "increase the salary with 2% for all employees", there's
obviously no need for a cursor here. The rules for the salary increase
may be more complex, but could very well still be implementable in a
single UPDATE statement. But say that for each employee you need to
call some external program to get the salary increase, at this point you
may have to resort to a cursor.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Gaetan <someone@.somewhere.com> wrote:

>I understand what you are saying but I fail to understand how else I could
use a
>relational processing to perform a row-at-a-time process. For example, in a
SP I need to
>apply some business logic on rows of a table matching specific criterias an
d return the
>output;
>Table XYZ -> CURSOR -> Business processing on each row -> insert into temp
table -> select
>* from temp table
>How could this type of processing be performed without the use of a CURSOR?
If I could get
>an order of magnitude performance gain, you bet that I will change a few st
ored procedures
>right away.
>Thanks.
The question is whether "Business processing on each row" can be done
relationally. Perhaps it can't, but I would not make that judgement
without knowing a lot more about that business processing. If you
care to share some of the requirements someone might have some
thoughts to share on whether they can be handled relationally.
One clear-cut case where cursors were required comes to mind, where
transactions had to be applied in strict sequence.
Roy