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

Related Posts::

basic of view
insert into view with multiple tables
Insert into view with single table
Instead of update trigger and after update trigger in sql
Instead of Insert trigger and after insert trigger in sql
instead of delete trigger and after delete trigger in sql
basic of trigger and interview quetions on trigger
Knowledged Queries and Interview Queries
Comman Table Expression
Functions In Sql