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 (http://blog.SQLAuthority.com) , BOL – WHILE
Community Initiatives
About Pinal Dave
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Follow @pinaldave
Send +Pinal Dave an email at pinal@sqlauthority.com-
- 63,331,720 (63 Million+)
SQL in Sixty Seconds
SQL Books
Funny Index Video
SQLAuthority Links
My Homepage
Windows Live Blog
--------------------
Top Downloads
PDF Downloads
Script Downloads
Script Bank
Favorite Scripts
All Scripts - 1
All Scripts - 2
All Scripts - 3
Top Articles
Best Articles
Favorite Articles - 1
Favorite Articles - 2
--------------------
> SQL Interview Q & A <
SQL Coding Standards
SQL FAQ Download
--------------------
Jobs @ SQLAuthority
About Nupur Dave
Top 3 Commenters
-
that was awesome. however i need some clarification via email.
Q. suppose I have a set of names in form of rows and each name has details corresponding to it below it in the form of rows. e.g
john smith
42vfgg
123wdcfdf
2qefg
kelly simpson
fwf3t
fr3fv
f5re
Obama Clinton
werf343v
454fvfg
4rgr
jehova shalom
3423vf
534tr
5df
54
alabama nelson
45yb
4544tgf
3463fg
t45
now suppose we want to delete some names we dont need together with there details, how do we go about the syntax? eg delete kelly simpson with his details and jehova shalom and his details
thanks
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
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
For version 2005 or later use
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
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?
@Garima
See this article where Pinal discusses how to separate comma-separated values. You should be able to do the same ting, changing comma (,) to pipe (|)
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
Thanks Brian but the method given in the link is not what I require.I am not saving my values in any file.
@Garima
I am sorry, i sent the wrong link!
Please look here:
http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/
Dear Sir,
I have a 2 columns having records like:
Col1 Col2
Fruits Apple
Fruits Mango
Fruits Orange
Sport Criket
Sport Football
Dress Jeans
Now i want to fetch result like :
Fruits
Apple
Mango
Orange
Sport
Criket
Football
Dress
Jeans
How can i fetch this result . Please help
dear sir,
i have this table:
LOAN BAL INT PI CRTERM STATE
1 10000 4.5 150.28 0 TN
2 20000 4.75 168.89 0 MS
3 30000 5 212.56 0 TN
4 40000 5.25 243.26 0 MS
5 50000 5.5 305.26 0 TN
6 60000 6 345.2 0 AL
i need to count how many times a certain state shows up plus each states balance added up.
please help
@lisa
SELECT State, COUNT(*), SUM(Bal) FROM mytab GROUP BY State;
I have table1 in that i have 2 column like
PERSON_NO Current_IND
101 1
101 -1
101 0
102 -1
103 0
103 -1
Now in result i want to identified person_no which has current_ind= -1
Condition 1: that same person_no should not have current_ind=1…
From above table my result will be :
Person_no Current_ind
102 -1
103 -1
(Note : 101 will not come in result as its having current_ind =1 )
@bhumika shah
Here’s one way:
WITH
table1(PERSON_NO, Current_IND)
AS
(
SELECT 101, 1 UNION ALL
SELECT 101, -1 UNION ALL
SELECT 101, 0 UNION ALL
SELECT 102, -1 UNION ALL
SELECT 103, 0 UNION ALL
SELECT 103, -1
)
SELECT
PERSON_NO,
MAX(Current_IND) Current_IND
FROM
table1
WHERE
Current_IND IN(-1, 1)
GROUP BY
PERSON_NO
HAVING
MAX(Current_IND) = -1;
Hi,
Thanks so much for this useful article. I am a newbie to MSSQL and I have a problem which I can’t solve yet.
There is a table which records Internet users (accountid) and their input/output bytes for a session. The columns of interest are (accountid, inbytes, outbytes) respectively. There are many users and a user may have more than 1 session (1 row in a table) for a day. Here is an example of that table:
day accountid inbytes(MB) outbytes(MB)
1/1/2010 32 80 3
1/1/2010 50 5 20
1/1/2010 32 1 15
2/1/2010 11 0 0
3/1/2010 50 100 24
.
.
.
29/1/2010 11 54 12
I need to calculate a user’s total data usage (inbytes+outbytes) for a given period (user will specify from which day to till which day). I then need to output it as a table on a webpage, like this:
AccountID Totalusage(MB)
11 2000
32 586
I can figure out the table drawing part I think. Problem is with the calculation of each user’s usage. I know it’s not so difficult but I am very new to MSSQL and I don’t have very good books.
Thanks so much for the time and help.
@Phyo
Select AccountID
,SUM(ISNULL(InBytes,0), ISNULL(OutBytes,0) TotalUsage
From MyTable
Where Day Between @StartDate AND @EndDate
Group BY AccountID
Here @StartDate and @EndDate are parameters that user will provide.
1. You can put this in a stored procedure that accepts StartDate and EndDate as a parameter.
2. Based on the format you are using Day, you can use convert function and style to compare dates.
Ex:
Select AccountID
,SUM(ISNULL(InBytes,0), ISNULL(OutBytes,0) TotalUsage
From MyTable
Where Convert (varchar,Day,102) Between Convert (Varchar,@StartDate,102) AND Convert (Varchar,@EndDate, 102)
Group BY AccountID
~Peace
@Phyo
Small Correction in SQL Statement, it should be Plus but not a comma,
Corrected SQL :
————————————-
Select AccountID
,SUM(ISNULL(InBytes,0)+ ISNULL(OutBytes,0) TotalUsage
From MyTable
Where Day Between @StartDate AND @EndDate
Group BY AccountID
~Peace.
Thanks so much Imran. It works like a charm!! So, GROUP BY is the function I was looking for. I was modifying an existing query file and when I first tried, MSSQL gave me an error saying that I need to put all the data fields in the select statement in GROUP BY clause. Now, I tried like you said and it works wonderfully. Thanks once again. Have a nice day.
Hi Sir,
How to get fast execution of this below code
ALTER PROC [dbo].[usp_PopulateNumber4] @Start int = NULL, @End int = NULL AS
DECLARE @Digit TABLE (digit int NOT NULL);
SET NOCOUNT ON;
DECLARE @i int = @Start;
BEGIN TRAN
WHILE @i <= @End
BEGIN
INSERT Number (Num) SELECT @I
SET @i += 1;
END
COMMIT
NSERT Number (Num)
select @start+number from master..spt_values
where type=’p’ and number between 0 and @end
Isn’t this exact example provided on the Microsoft reference website? :-)
http://msdn.microsoft.com/en-us/library/ms178642.aspx
My table (Bill) is as following
Doc_no Date ItemName Qty
A1001 27/09/10 Pen 5
Select * From Bill where Doc_No = ‘A1001′
I want to repeat above line number of Qty Times (5 Times) with qty 1.
Plz help, thnx in advance.
So you want to get 5 duplicate entries?
DECLARE @Digit TABLE (digit int NOT NULL);
SET NOCOUNT ON;
DECLARE @i int = @Start;
BEGIN TRAN
WHILE @i <= @End
BEGIN
INSERT Number (Num) SELECT @I
SET @i += 1;
END
COMMIT
Note that this assignment syntax will work from version 2008 onwards
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
Hi Pinal Dave and Forum,
I was hoping if you guys can point me to the right direction.
I do have the following table scenario
Columns: Year, Period, MonthToDate, YearToDate
Row1: 2010, 0, 100, 100
Row2: 2010, 3, 200, 300
Row3: 2010, 9, 300, 600
I was hoping to create a While script that will insert the missing periods (up to period 12), and then place 0 for the MonthToDate, while also making sure the YearToDate values are correct.
As such, I was hoping to get the following result set:
Columns: Year, Period, MonthToDate, YearToDate
Row1: 2010, 0, 100, 100
Row2: 2010, 1, 0, 100
Row3: 2010, 2, 0, 100
Row4: 2010, 3, 200, 300
Row5: 2010, 4, 0, 300
Row6: 2010, 5, 0, 300
Row7: 2010, 6, 0, 300
Row8: 2010, 7, 0, 300
Row9: 2010, 8, 0, 300
Row10: 2010, 9, 300, 600
Row11: 2010, 10, 0, 600
Row12: 2010, 11, 0, 600
Row13: 2010, 12, 0, 600
It looks like I would also need some grouping on the script.
Any ideas you have would be greatly appreciated.
Thank you for your time.
Regards,
Eric
Boa tarde,
como faz qual formulas horario para turno? exemplo
hora — turno
6:15 — 1
9:00 — 1
15:50 — 2
23:05 — 3
Hi Pinal and Forum,
Your blog is very helpful. But i am stuck with something different. I have table having following columns.
C_ID C_ Rank NewColumn(Cycle) Date
42 A 1 October 14, 2010
42 B 1 October 26, 2010
42 A 2 February 16, 2011
43 A 1 December 17, 2010
44 A 1 July 28, 2010
44 B 1 August 10, 2010
44 A 2 January 11, 2011
44 B 2 January 28, 2011
45 A 1 July 30, 2010
45 B 1 August 9, 2010
45 B 1 September 24, 2010
45 A 2 April 5, 2011
45 B 2 April 26, 2011
Actually table has three columns(C_ID, C_rank, Date). I want to generate one more column called Cycle in such a way that for each C_ID, it should generate the number start from one and increment the number from next C_rank ‘A’ (a shown above).
I tried using row_number, but no luck.
May be some loop option till next C_Rank ‘A’ works.
This is urgent.
Any help is appreciated.
Thanks.
@Punia.
I am sure there is a better way of doing this, but below script is the quiest way….
/*
THIS IS SETUP TABLE, WITH SOME SAMPLE DATA
*/
if exists (select 1 from tempdb.sys.objects where name like ‘#Temp_Table%’)
drop table #Temp_Table
create table #Temp_Table
(
C_ID int
, C_Rank char(1)
, Date datetime
, NewColumn int
)
insert into #Temp_Table
(
C_ID
, C_Rank
, Date
)
select 42, ‘A’, ’10/14/2010′
union all
select 42, ‘B’, ’10/26/2010′
union all
select 42, ‘B’, ’10/14/2010′
union all
select 42, ‘C’, ’10/26/2010′
union all
select 42, ‘A’,’02/16/2011′
union all
select 43, ‘A’, ’12/17/2010′
union all
select 44, ‘A’, ’07/28/2010′
union all
select 44, ‘B’, ’08/10/2010′
union all
select 44, ‘A’, ’01/11/2011′
union all
select 44, ‘B’, ’01/28/2011′
union all
select 44, ‘C’, ’10/14/2010′
union all
select 44, ‘D’, ’10/26/2010′
Select ‘Original Data’ Comment
,*
from #Temp_Table
/*
This would be Actual Script to get the New ID based on information you provided
*/
Declare @Count int
,@C_ID int
,@C_Rank char(1)
,@total_Count int
,@Count_Partition int
,@Previous_ID int
Declare @Table Table (ID int IDENTITY(1,1), C_ID int, C_Rank char(1), Date datetime, NewColumn int )
Set @Count = 1
Set @Count_Partition = 0
insert into @Table
Select *
from #Temp_Table
Select @total_Count = ISNULL(MAX(ID),0)
from @Table
While @Count < = @total_Count
Begin
Select @C_ID = C_ID
,@C_Rank = C_Rank
From @Table
Where ID = @Count
If @Count = 1
Set @Previous_ID = @C_ID
If @Previous_ID != @C_ID
Set @Count_Partition = 1
Else If @C_Rank = 'A'
Set @Count_Partition = @Count_Partition + 1
update @Table
Set NewColumn = @Count_Partition
Where ID = @Count
Set @Previous_ID = @C_ID
Set @Count = @Count + 1
End
Select C_ID
, C_Rank
, [Date]
, NewColumn
from @Table
–Drop table #Temp_Table
~ IM.
@Punia,
One Important assumption I made here was, First C_Rank Value for any C_ID would be ‘A’. If not, you have to modify script accordingly.
~IM.
Thanks Imran,
This solves my problem.
I have one more question. I have about 14,000 rows. So when i use your logic, it gives the correct result, but performance degrades (takes about 4 minutes to execute). Is there is any solution to decrease the execution time.
Thanks again for your help.
Punia
@Punia,
See, I knew this would kill performance. Use this for now. In couple of days I will update another version which I can assure you will be much faster than this.
But, I wonder, 14,000 records execution time is 4 minutes… That is worst than I expected.
What is your Server Specs ?
~ IM.
Hi Dave,
My query is , im having table sale
Sale{Cleant_name,Product}
eg:
Client_name | Product
a | 123
a | 321
b | 123
output must be in this pattern:
Client_name | product1 | product2
a | 123 | 321
b | 123
Thanks in advance
waiting for reply
DRY does not need a while loop — it’s SQL. try:
SELECT @SQL = @SQL +[Text]
FROM @tblSQL
WHERE id <= @intCount
ORDER BY id
I have problem in deleting records from the table.
Below is the requirement.
I have table UserLogins(UserId,Logindate)
Data Like
UserId Logindate
111 01/12/2012
111 02/12/2012
111 03/12/2012
111 04/12/2012
222 01/12/2012
222 02/12/2012
222 03/12/2012
222 04/12/2012
333 01/12/2012
333 02/12/2012
333 03/12/2012
333 04/12/2012
I want to delete the records from this table and I want retain only last 3 recent records for each userid.
Any help on this Appreciated.
sir, i want to create the result as follows..
the id and date is here in tablw.i want to collect the sum of datediff in as order as employee id…in table,,date in punch in and punch out…iwant to diff alternate table…then their sum …which is considered as slno.. ie,
slno 1
employeeId 1
time 6(in hr)
can u plz help me sir,,
I try your explanation, and query running well, see detail http://sqltosql.blogspot.com/search/label/sql%20while
forgive me, I forgot to say
thanks a lot to Mr SqlAuthority
[...] Simple Example of WHILE Loop with BREAK and CONTINUE One of the most popular blog posts of my blog has been from this week. Lots of people do not know how to run the WHILE loop in SQL Server and this blog post precisely describes the same. [...]