how to break out of a cursor and out of a SP all together

[Matt]

Hi

I am trying to figure out how to exit a cursor loop if a specified
condition occurs.

I have a select count(*) on a table like this

select lagplats, count(*) from arsi where artnr = '1440'

if that count(*) is > 1 I want to break that cursor loop and fetch the
next row into the cursor and continue.

however if that count(*) > 5 I need to exit the entire procedure and
not try and fetch the next row into the cursor

I find this hard to explain and the code is long and complicated so I
hope it is possible to understand what I am after

rgds

Matt

[David Portas]

Not the answer you expect maybe, but why are you writing a cursor at all?
Your code already sounds very complex. Are you sure there isn't a set-based
solution? Cursors should be a last resort so if you need help with a
non-cursor solution please post DDL, some sample data as INSERT statements
and show your required result.

--
David Portas
SQL Server MVP
--

[David Portas]

Your loop logic could be something like this. As I suggested before, this
sort of thing can usually be replaced by more efficient set-based
SELECT/INSERT/UPDATE/DELETE statements.

WHILE @@FETCH_STATUS = 0
BEGIN
...
DECLARE @cnt INTEGER

SET @cnt =
(SELECT COUNT(*)
FROM arsi
WHERE artnr = '1440')

IF @cnt > 5
RETURN

IF ()<=1
BEGIN
...
END

FETCH NEXT...

END
--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Matt (ma**@fruitsalad.org) writes:

I am trying to figure out how to exit a cursor loop if a specified
condition occurs.

I have a select count(*) on a table like this

select lagplats, count(*) from arsi where artnr = '1440'

if that count(*) is > 1 I want to break that cursor loop and fetch the
next row into the cursor and continue.

however if that count(*) > 5 I need to exit the entire procedure and
not try and fetch the next row into the cursor

I find this hard to explain and the code is long and complicated so I
hope it is possible to understand what I am after

To exit a loop, you can use BREAK. You can also use CONTINUE to
immediately start from the beginning of the loop.

But you should probably not use a statement like the above in a loop,
since it produces a result set. (Also the statement you posted does
not compile, since there is no GROUP BY for the lagplats column.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp