how can i use for loop in stored procedure in sql server ??

i want to know that how can i use for loop in stored procedure in MS SQL Server ??

please refer the below strored procedure ...

CREATE PROCEDURE usp_SaveNewQuestion
    (
      @uid INT ,
      @Question VARCHAR(200) ,
      @correctOpt VARCHAR(50)
    )
AS 
    BEGIN

        INSERT  INTO Details
                SELECT  uname ,
                        pwd ,
                        email ,
                        login_time ,
                        @Question ,
                        @correctOpt ,
                        logout_time ,
                        comment
                FROM    Details
                WHERE   uid = @uid
        

    END
GO

Open in new window


how can i use for loop in above stored procedure ??
Parth48Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lalitgadaConnect With a Mentor Commented:
USE AdventureWorks;
GO
DECLARE @Flag INT
SET @Flag = 1
WHILE (@Flag < 10)
BEGIN
BEGIN
PRINT @Flag
SET @Flag = @Flag + 1
END
IF(@Flag > 5)
BREAK
ELSE
CONTINUE
END
WHILE loop can use SELECT queries as well. You can find following example of BOL very useful.
USE AdventureWorks;
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';
0
 
Pratima PharandeCommented:
sql u have to use cursor

here is the simple cursor implementation
syntax:
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

Open in new window


here is the complate example
http://www.mssqlcity.com/Articles/General/UseCursor.htm
0
 
Jini JoseSenior .Net DeveloperCommented:
can u please tell what for you using the loop in your stored procedure ?
for inserting the data ?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pratima PharandeCommented:
you can use while loop like this

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
0
 
Parth48Author Commented:
hello @pratima_mcs: Thanks , in both the way u r right , but which is the better way according to u , using cursor or while loop ??
0
 
Pratima PharandeCommented:
it really depends on your situation. I almost always use a cursor to loop through records when necessary. The cursor format is a little more intuitive for me and, since I just use the constructs to loop through the result set once, it makes sense to use the FAST_FORWARD cursor. Remember that the type of cursor you use will have a huge impact on the performance of your looping construct.

for more details refer

http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741
0
 
h_aravindCommented:
You can use a While loop rather than using a cursor. cursors are performance hit.
0
 
Parth48Author Commented:
hi @lalitgada: where i can download AdventureWorks database ??

is it on MSDN ??
0
 
Pratima PharandeCommented:
AdventureWorks is default database on SQl server
0
 
h_aravindCommented:
Adventure works, northwind database etc can be downloaded from Microsoft website or from codeplex.com
0
 
Parth48Author Commented:
hi @h_aravind: can u please post the download link ??
0
 
h_aravindCommented:
Can you try this?

http://sqlserversamples.codeplex.com/

This has details for both 2005 & 2008
0
 
Parth48Author Commented:
hi @h_aravind: thanks for the link ....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.