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 01, 2013 5:34 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:03 AM
Points: 186, Visits: 473
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 01, 2013 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
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 01, 2013 7:45 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:03 AM
Points: 186, Visits: 473
Thats great thank you
Post #1448420
Posted Wednesday, May 01, 2013 8:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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 02, 2013 6:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:43 PM
Points: 4,128, Visits: 5,836
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