Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Droping out of a cusor Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 5:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:35 AM
Points: 193, Visits: 504
Hi what’s the best to way to drop out of a cursor if a condition is meet?

Many thanks
Post #1448375
Posted Wednesday, May 1, 2013 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,899, Visits: 32,113
You are looking for the BREAK keyword.
a simple example to break out of what would otherwise be an endless loop:
--RAISERROR immediate message
--print error immediately in batch
DECLARE
@i INT,
@err VARCHAR(100)
--set @i=1
WHILE 0 = 0
BEGIN
SET @err = 'Progress So Far: Step ' + CONVERT(VARCHAR(30), ISNULL(@i, 1)) + ' completed.'
RAISERROR (@err,
0,
1) WITH nowait
WAITFOR delay '00:00:02'
SET @i=ISNULL(@i, 1) + 1
IF @i > 5
BREAK;
END
PRINT 'We Broke out of the loop!'




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1448391
Posted Wednesday, May 1, 2013 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:35 AM
Points: 193, Visits: 504
Thats great thank you
Post #1448420
Posted Wednesday, May 1, 2013 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
Edward-445599 (5/1/2013)
Hi what’s the best to way to drop out of a cursor if a condition is meet?

Many thanks


Are you sure you even need a cursor? Given that you want to break out of one I am guessing you are doing some sort of RBAR processing. Maybe the best way out of your cursor is to not have one at all.



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1448433
Posted Thursday, May 2, 2013 6:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 4,360, Visits: 6,198
Sean Lange (5/1/2013)
Edward-445599 (5/1/2013)
Hi what’s the best to way to drop out of a cursor if a condition is meet?

Many thanks


Are you sure you even need a cursor? Given that you want to break out of one I am guessing you are doing some sort of RBAR processing. Maybe the best way out of your cursor is to not have one at all.



+100


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1448789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse