how to use for loop in sql server 2008

there is no for loop available in SQL use while instead

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

We have WHILE Loop

DECLARE @A INT

SELECT @A =65



WHILE(@A <=90)

BEGIN

	PRINT CHAR(@A)

	SELECT @A = @A + 1

END

GO

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

There are loop structures(WHILE, CURSORS) in SQL Server..
Mostly we can do queries without using loops in SQL Server... Let us know the requirement. we will provide you the direction without using loops if possible

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Dear Shanky11,

There is no for loop in SQL. But alternate to this is given in below examples:-
Below are the Simple Example of WHILE Loop With CONTINUE and BREAK Keywords.

1) Example of WHILE Loop

DECLARE @intFlag INT

SET @intFlag = 1

WHILE (@intFlag <=5)

BEGIN

PRINT @intFlag

SET @intFlag = @intFlag + 1

END

GO

ResultSet:
1
2
3
4
5

2) Example of WHILE Loop with BREAK keyword

DECLARE @intFlag INT

SET @intFlag = 1

WHILE (@intFlag <=5)

BEGIN

PRINT @intFlag

SET @intFlag = @intFlag + 1

IF @intFlag = 4

BREAK;

END

GO

ResultSet:
1
2
3

3) Example of WHILE Loop with CONTINUE and BREAK keywords

DECLARE @intFlag INT

SET @intFlag = 1

WHILE (@intFlag <=5)

BEGIN

PRINT @intFlag

SET @intFlag = @intFlag + 1

CONTINUE;

IF @intFlag = 4 -- This will never executed

BREAK;

END

GO

ResultSet:
1
2
3
4
5

4) Using BREAK and CONTINUE with nested IF...ELSE and WHILE

USE AdventureWorks2012;

GO

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300

BEGIN

   UPDATE Production.Product

      SET ListPrice = ListPrice * 2

   SELECT MAX(ListPrice) FROM Production.Product

   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500

      BREAK

   ELSE

      CONTINUE

END

PRINT 'Too much for the market to bear';

5) Using WHILE in a cursor

DECLARE Employee_Cursor CURSOR FOR

SELECT EmployeeID, Title 

FROM AdventureWorks2012.HumanResources.Employee

WHERE JobTitle = 'Marketing Specialist';

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0

   BEGIN

      FETCH NEXT FROM Employee_Cursor;

   END;

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor;

GO

Happy coding.

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator