• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 781
  • Last Modified:

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 ??
0
Parth48
Asked:
Parth48
  • 4
  • 4
  • 3
  • +2
1 Solution
 
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 Jose.Net Team LeadCommented:
can u please tell what for you using the loop in your stored procedure ?
for inserting the data ?
0
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
lalitgadaCommented:
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now