Skip to main content

Exit a T-SQL Cursor When Condition is met

Have you ever wanted to exit from a cursor when a condition is met? I wanted to do it. So this is how I did it.

DECLARE @Field1 AS INT
DECLARE @Field2 AS INT

DECLARE CursorName CURSOR READ_ONLY
FOR

SELECT
Field1,
Field2
FROM TableName

OPEN CursorName

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

WHILE @@FETCH_STATUS = 0
BEGIN

IF @Field1 = 1
BEGIN
GOTO ENDCURSOR
END

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

END

ENDCURSOR:
CLOSE CursorName
DEALLOCATE CursorName

I have set my fonts to bold where you want to notice.

So that's all I hope you will get something out of it and it is true that this is not a big deal. :)
Labels:

Comments

Ghost said…
It's strange that T-SQL doesn't have a break statement. GOTO statements are pretty old stuff now.
December 2, 2009 at 12:52 AM
Le Dan said…
Why not just declare a variable as BIT and make it a further condition for the while-loop like this:

DECLARE @Field1 AS INT
DECLARE @Field2 AS INT
DECLARE @StopWhile BIT

DECLARE CursorName CURSOR READ_ONLY
FOR

SELECT
Field1,
Field2
FROM TableName

OPEN CursorName

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

WHILE @@FETCH_STATUS = 0 AND @StopWhile = 0
BEGIN

IF @Field1 = 1
BEGIN
SET @StopWhile = 1
END

FETCH NEXT FROM CursorName
INTO @Field1, @Field2

END

CLOSE CursorName
DEALLOCATE CursorName

Just another possibility :)
January 13, 2010 at 8:23 AM
Oshadha said…
Yes this bit variable thing will be a effective way as I feel. Thanks Le Dan.
February 15, 2010 at 9:15 PM
Post a Comment

Popular posts from this blog

How to use DataBinder.Eval in HyperLink NavigationUrl - ASP.net

I wanted to append page Url with DataBinder.Eval value in ASP.NET hyperlink control on ASP.NET HTML source. I was trying it using many ways and also search for resources on web but I could not find any useful thing. I thought this will help u guys to get an idea about appending two strings. Solution as follows. <asp:HyperLink ID="customerHyperLink" runat="server" Text= ' ' NavigateUrl=' ' ></asp:HyperLink> You can see how I have appended these two value in NavigateUrl property. Hope some one will get the advantage of this post.
2 comments
Read more

Common Design Principles

There are number of common design principles that, like design patterns, best practice over the years to build maintainable software. I'm up to describe some widely used design principles though out the post. Following common principle are extracted by the same book that I mentioned before ( Professional ASP.Net Design Patterns - Scott Millet ). Principles are as follows: Keep It Simple Stupid (KISS) One common issue in software programming is over-complicating a solution. So main concern of this principle is keep the code simple but not simplistic. Eventually this will avoid unnecessary complexities. Don't Repeat yourself (DRY) Main concern of this principle is to avoid the repetition. In other words this is all about abstracting out the common functionalities into a single place. Ex: If there is a price calculation method in a system. It should lay in a single place there. Tell Don't Ask The Tell, Don’t Ask principle is closely aligned with encapsulation and the assignin...
1 comment
Read more