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,
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 (|)
https://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:
https://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;
Select PERSON_NO,Current_IND
from table1
where Current_IND = -1 and
PERSON_NO not in (select PERSON_NO from #temp where Current_IND =1 and 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? :-)
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-2017
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