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.
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 ...
how can i use for loop in above stored procedure ??
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
how can i use for loop in above stored procedure ??
Who is Participating?
sql u have to use cursor
here is the simple cursor implementation
syntax:
here is the complate example
http://www.mssqlcity.com/Articles/General/UseCursor.htm
here is the simple cursor implementation
syntax:
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]
here is the complate example
http://www.mssqlcity.com/Articles/General/UseCursor.htm
you can use while loop like this
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
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 ??
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
for more details refer
http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741
Adventure works, northwind database etc can be downloaded from Microsoft website or from codeplex.com
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.
-
Course of the Month8 days, 11 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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';