how to use for loop in sql server 2008

Posted by Shanky11 under Sql Server on 2/25/2013 | Points: 10 | Views : 92388 | Status : [Member] | Replies : 6
how can we use for loop in sql2008
is there looping ???




Responses

Posted by: Satyapriyanayak on: 2/25/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
http://msdn.microsoft.com/en-us/library/ms139956%28v=sql.105%29.aspx
http://blog.sqlauthority.com/2007/10/24/sql-server-simple-example-of-while-loop-with-continue-and-break-keywords/
http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741

If this post helps you mark it as answer
Thanks

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

Posted by: Murugavelmsc on: 2/25/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Pandians on: 2/25/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Bageshkumarbagi on: 9/3/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Refer this link,
http://technet.microsoft.com/en-us/library/ms178642.aspx

Thanks,
Bagesh Kumar Singh
Mark as answer if you have been satisfied

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

Posted by: Bandi on: 9/3/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Allemahesh on: 9/4/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Login to post response