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';
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – WHILE
SQL SERVER – Simple Example of WHILE Loop with BREAK and CONTINUE
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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?