WHILE statement sets a condition for the repeated execution of an SQL statement or statement block. Following is very simple example of WHILE Loop with BREAK and CONTINUE.
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';
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
62 Comments. Leave new
hi .ok
Is there a time out period during which the value in the while condition needs to be returned? I have a job that does a check as follows and purges old data:
while exists(select top 1 * from Records (nolock) where filetimeStamp < @d1)
begin
set rowcount 10000
delete from Records where filetimeStamp < @d1
end
The query ‘select top 1 * from Records (nolock) where filetimeStamp < @d1’ takes 8 mins to return a value, as the table has around 322 millon rows in it. No indexes on the table. The job is not deleting data properly, and I was wondering if the query in the while condition is timing out causing the issue.
I have a table with associate names. Another table with one field which is associate dealers. There are no common fields possible between the 2 tables to do any kind of join.
i want to create a 3rd table with 2 fields, where each associate name in the 1st table is inserted with every dealer in the second table. How do I do this? Thanks
thank u sir. but i have a problem in sql query and the problem is , i want to select 2 alternative rows of all the table .
e.g if account number are lioke 1,2,3,4,5,6,7,8,9,10
i want to select 1,2,5,6,9,10 like that
but i cant’t do that . please help me as soon as possible .i am a fresher i dont have so much knowldge in sql server but i keen to know, can you help me sir . Please give me a book name throw which i can got som knowldge.
thanking you,
Bishnu.
Can we add new column to a view table
You need to alter the view to add that column
Yes. U can insert a new column using an insert script itself…
Hello,
Please help me ,
I m not a DBA but i wanna to create log table
where i have to compare two tables and loop through the column names in two tables…
to find column name i used
SELECT SC.NAME AS “Column Name”
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
WHERE SO.xtype = ‘U’
and SO.NAME =’tblnew’
ORDER BY SO.[name], SC.colid
and for Count
SELECT Count(SC.NAME) AS “Column count”
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
WHERE SO.xtype = ‘U’
and SO.NAME =’tblnew’
this new table i have to compare with the old how to do this…
how to improve select query performance?
hi
how to select a number of random rows with replacement
(bootstrapping) in sql server 2005
thanx in advance
Order by newid()
Hi,
I have a query like given…Is there any way to give the RoomName dynamically. I have several roomnames in a table. So its not possible to write case for all of them like i have done here. Pls help ASAP. Thnx in advance.
query
——–
SELECT ItemCode,Sum(Estimate) as projecttotal,
SUM(CASE WHEN RoomName =’Room1′ THEN Estimate ELSE 0 END ) AS [Room1],
SUM(case when RoomName=’Room2′ then Estimate else 0 end) as [Room2]
FROM ReportDataTable
GROUP BY ItemCode
You can use dynamic PIVOT for this
Refer this for more informations
For version 2000 use
For version 2005 or later use
thank u very much for the information.
please how to use multi while loop in sql server
while
–stuff
while
–stuff
end
end
@khaled
This is a small example…
Declare @Var1 int
Declare @Var2 int
Set @Var1 = 1
While @Var1 < = 10
Begin
Print 'This is outer loop Value is :'+Convert (varchar, @Var1)
Set @Var2 = 1
While @var2 <= 10
Begin
Print ' This is Inner Loop Value is:'+Convert (varchar, @Var2)
Set @Var2 = @var2+1
End
Set @Var1 = @var1+1
End
~IM
@khaled
Declare @Var1 int
Declare @Var2 int
Set @Var1 = 1
While @Var1 < = 10
Begin
Print 'This is outer loop Value is :'+Convert (varchar, @Var1)
Set @Var2 = 1
While @var2 <= 10
Begin
Print ' This is Inner Loop Value is:'+Convert (varchar, @Var2)
Set @Var2 = @var2+1
End
Set @Var1 = @var1+1
End
Heres a proc I made using your sample:
CREATE PROCEDURE dbo.prcGetStoredProcedureCode
@strStoredProcedureName VARCHAR(100) = ‘dbo.sp_helptext’
AS
BEGIN
/*
Samples:
EXEC dbo.prcGetStoredProcedureCode
EXEC dbo.prcGetStoredProcedureCode ‘dbo.prcRebatesExportFormat800’
*/
DECLARE @tblSQL TABLE (id int IDENTITY(1,1), [Text] varchar(max))
INSERT INTO @tblSQL
EXEC sp_helptext @objname = @strStoredProcedureName
DECLARE @strSQL VARCHAR(MAX)
DECLARE @intFlag int
DECLARE @intCount int
SET @intCount = (SELECT COUNT(*) FROM @tblSQL)
SET @intFlag = 1
SET @strSQL = ”
WHILE (@intFlag @intCount)
BREAK
ELSE
CONTINUE
END
–SELECT * FROM @tblSQL
SELECT @strSQL AS strSQL
END
I forgot about html.
There is a Less than character
between the two fields in line:
WHILE (@intFlag @intCount)
Let me try this again:
CREATE PROCEDURE dbo.prcGetStoredProcedureCode
@strStoredProcedureName VARCHAR(100) = ‘dbo.sp_helptext’
AS
BEGIN
/*
EXEC dbo.prcGetStoredProcedureCode
EXEC dbo.prcGetStoredProcedureCode ‘dbo.prcRebatesExportFormat800’
*/
DECLARE @tblSQL TABLE (id int IDENTITY(1,1), [Text] varchar(max))
INSERT INTO @tblSQL
EXEC sp_helptext @objname = @strStoredProcedureName
DECLARE @strSQL VARCHAR(MAX)
DECLARE @intFlag int
DECLARE @intCount int
SET @intCount = (SELECT COUNT(*) FROM @tblSQL)
SET @intFlag = 1
SET @strSQL = ”
WHILE (@intFlag < (@intCount + 1))
BEGIN
SET @strSQL = @strSQL + (SELECT [Text] FROM @tblSQL WHERE id = @intFlag)
SET @intFlag = @intFlag + 1
IF(@intFlag > @intCount)
BREAK
ELSE
CONTINUE
END
SELECT @strSQL AS strSQL
END
Dear Sir,
Can u tell me how to insert multiple column records into a coulmn with comma separator.
I hav two column called col1,col2
Col1, col2
1 1
1 2
1 3
2 a
2 b
2 c
3 p
3 q
3 r
I want the result should be like as follows :
col1 col2
1 1,2,3
2 a,b,c
3 p,q,r
How do I get the same can u please help me regarding this ?
[quote]
Dear Sir,
Can u tell me how to insert multiple column records into a coulmn with comma separator.
I hav two column called col1,col2
Col1, col2
1 1
1 2
1 3
2 a
2 b
2 c
3 p
3 q
3 r
I want the result should be like as follows :
col1 col2
1 1,2,3
2 a,b,c
3 p,q,r
How do I get the same can u please help me regarding this ?[/quote]
In Reply To This
Set Xact_Abort On
Select Col1 + ‘,’, Col2 As Col3 From TableName With (NoLock)
Thanks, much appreciated
Hi I have a problem while fetching value’s in SQL server 2005.
I have a table called Schedule where i have these column and value upto 7 day.
Empid Day Mst Met Est Eet Slt
00012 1 1:00AM 2:00AM 3:00PM 4:00PM 15
2
3
I need a solution for fetching the value,
1-If slt (slot) is 10 than in betwee Mst time and Met Time i need to fetch these value as like ?
1:00
1:15
1:30
1:45
2:00
same as in Est and Eet time according to slot
2-If i send the day parametre (1,2,3) than for particular day it ll happen and the date for particular day should come in a temp column infront of that?
Can any one give the solution of this problem,i need.
Hi,
I have text values in one column and they are seperated by ‘|’ delimiter.
eg:-
234|33|432|44
result is coming as:-234
Expected result is 234
33
432
44
When I split them I am still getting only the first value and not all values seperated.
How can I get all the seperated values?