Monday, 17 June 2013

loop in sql server

In this article we well discuss about a WHILE loop.

1) What is while loop ? and what is difference between while loop and cursor ?

A) While loop ::

Sets a condition for the repeated execution of an SQL statement or statement block.
The statements are executed repeatedly as long as the specified condition is true.
The execution of statements in the WHILE loop can be controlled from inside the
loop with the BREAK and CONTINUE keywords.

B) Cursor ::
A cursor is a set of rows together with a pointer that identifies a current row.
In other word, Cursor is a database object used by applications to manipulate data
in a set on a row-by-row basis, its like record-set in the ASP and visual basic.


Syntax of while loop::
while (Condition)
begin
-- write your code here
end
go

1)Simple example of while loop::


DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=12)
BEGIN
PRINT 'you come in loop for ' + Convert(varchar, @intFlag ) + ' times'
SET @intFlag = @intFlag + 1
END
GO


2) Example of WHILE Loop with BREAK keyword

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT 'you come in loop for ' + Convert(varchar, @intFlag ) + ' times'
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO


3) Example of WHILE Loop with CONTINUE and BREAK keywords


DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT 'you come in loop for ' + Convert(varchar, @intFlag ) + ' times'
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO

No comments:

Post a Comment

8451831077