1) Example of WHILE Loop
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 END GO
ResultSet:
1
2
3
4
5
2) Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 IF @intFlag = 4 BREAK; END GO
Result
Set:
1
2
3
3) Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 CONTINUE; IF @intFlag = 4 -- This will never executed BREAK; END GO
Result Set:
1
2
3
4
5
I hope you find this simple blog post helpful. Let me know what you think of this blog post. Please leave your views in the comment sections.
Reference: Pinal Dave (https://blog.sqlauthority.com)
182 Comments. Leave new
hi i have a string like
HHHHHHsuhhhsusu each character represents a date starting from 28/07/2012 and im trying to select it or create a table
create table #calender (string as char(5)COLLATE SQL_Latin1_General_CP1_CI_AS, dat datetime)
declare @counter as int
set @counter = 3
while (@counter < 367)
begin
–print @counter
–insert into #calender (string,dat)
select distinct substring (CCAL_Available_Days
,@counter,1), dateadd (day,@counter,'2012-07-28 00:00:00.000') date from dbo.CCALCalend
where CCAL_Year = '2012'
set @counter = @counter+1
–select * from #calender
end
drop table #calender
i am getting the error
The column "string" cannot be modified because it is either a computed column or is the result of a UNION operator.
can you please help
declare @i int
declare @n int
set @i=0
set @n=10
while(@i<=@n)
BEGIN
PRINT 'A'
set @i=@i+1
END
i have doubt when i use the variables i and n as integer type ,A is printed 11
times
declare @i varchar(10)
declare @n varchar(10)
set @i=0
set @n=10
while(@i<=@n)
BEGIN
PRINT 'A'
set @i=@i+1
END
when i use the variables i and n as varchar ,A is printed twice can you please tell me the reason y this could be like this
When you use the Variable as varchar, it will take the ASCII value of 0 and 10 because they are treated as character, ASCII(0) = 48 and ASCII(10)=49, when you do the comparison on character value in SQL, SQL do that on its ASCII value. That is why your loop work for two times i.e. for 48 to 49.
Thanks for helping!
please help am trying to print a loop value outside the loop so i can add it on
select @val = loop_variable
can you please provide sample?
Hi i have one city table. in that i have to calculate the distance url between those cities. for example i have 5 city names like chennai,hyderabad,kolkuta, delhi, and mumbai. in that i have to calculate distance url like chennai to hyderabad, chennai to kokata, chennai to delhi, chennai to mumbai. i have query for single city but we have many cities in that table. i want to run the query for all cities in single query please help me….
Hello sir,
I need help from you
i have two columns
let me give example
masterid attributename attributevalue
————- ——————– —————–
1 mode_code barge
1 sap_code B
1 Description g
here i know how many attribute names are there so i can use cases here to update.
UPDATE CORE.MasterAttr
SET AttributeValue = (
CASE AttributeName
WHEN ‘DESCRIPTION’ THEN ‘Barge’
WHEN ‘SAP_MODE’ THEN ’01’
WHEN ‘MODE_CODE’ THEN ‘B’
END
) WHERE MasterId=1
but in real scenario i dont know how many attribute name will be available.
so i need to write storedprocedure .the input values will come from c#.
suppose for master id 2 i can have 12 attribute names and i have to update attribute vales for those attribute names.
if we give give masterid 4 and we can get 15 attribute names etc.
so how can we dynamically loop it and write stored procedure sir.it will really helps me alot.can u please help me on this.
Hi Pinal,
Hope you are doing good.
The looping query pasted below works fine in updating ‘hour’ in a date time column using NTILE().
+++++++++++++++++++
DECLARE @loop int
DECLARE @inc int
SET @inc=1
WHILE (@inc<=24)
BEGIN
;with up AS
(
SELECT salesorderdetailid,ntile(24) OVER (order by productid ) as ntile24
from sales.salesorderdetail
)
UPDATE sales.salesorderdetail
SET modifieddate=DATEADD(hh,@inc,modifieddate)
from sales.salesorderdetail
where salesorderdetailid in
(
select salesorderdetailid from up
where NTILe24 =@inc
)
PRINT @inc
SET @inc=@inc+1
END
–select * from sales.salesorderdetail tablesample (1000 rows)
+++++++++++++++++++
Is there any simplest way to solve this problem?
OR
If the query can be modified without using CTE?
Kindly suggest how to increase the query performance when handling these situations.
Thanks,
Arunraja.
I have created a view obtaining the RunningTotal joining the query] to itself that uses ROW_NUMBER() OVER (PARTITION BY Location ORDER BY Location, Time
ON a.Row >= b.Row AND a.location = b.location
SUM(b.Qty) AS RunningTotal
Here is a sample of the results – Question following sample
Rownumber Location Time X1 X2 X3 Qty RunningTotal
1 EAST 10/23/13 0:38 0.29719 0.1893 0.13493 250 250
2 EAST 10/23/13 0:51 0.46958 0.36483 0.22245 250 500
3 EAST 10/23/13 1:43 0.46958 0.36483 0.22245 -250 250
4 EAST 10/23/13 1:58 0.46958 0.36483 0.22245 250 500
5 EAST 10/23/13 2:10 0.46958 0.36483 0.22245 250 750
6 EAST 10/23/13 2:23 0.23345 0.19788 0.09304 -250 500
7 EAST 10/23/13 3:22 0.23345 0.19788 0.09304 -250 250
8 EAST 10/23/13 3:38 0.23345 0.19788 0.09304 -250 0
9 EAST 10/23/13 3:52 0.23345 0.19788 0.09304 250 250
10 EAST 10/23/13 4:09 0.29719 0.1893 0.13493 250 500
11 EAST 10/23/13 4:26 0.29719 0.1893 0.13493 250 750
12 EAST 10/23/13 4:41 0.29719 0.1893 0.13493 -250 500
13 EAST 10/23/13 5:07 0.29719 0.1893 0.13493 -250 250
14 EAST 10/23/13 5:13 0.23345 0.19788 0.09304 -250 0
15 EAST 10/23/13 5:19 0.23345 0.19788 0.09304 250 250
16 EAST 10/23/13 5:23 0.29719 0.1893 0.13493 250 500
1 WEST 10/23/13 0:38 0.29719 0.1893 0.13493 250 250
2 WEST 10/23/13 0:51 0.46958 0.36483 0.22245 250 500
3 WEST 10/23/13 1:43 0.46958 0.36483 0.22245 -250 250
4 WEST 10/23/13 1:58 0.46958 0.36483 0.22245 250 500
5 WEST 10/23/13 2:10 0.46958 0.36483 0.22245 250 750
6 WEST 10/23/13 2:23 0.23345 0.19788 0.09304 -250 500
7 WEST 10/23/13 3:22 0.23345 0.19788 0.09304 -250 250
8 WEST 10/23/13 3:38 0.23345 0.19788 0.09304 -250 0
9 WEST 10/23/13 3:52 0.23345 0.19788 0.09304 250 250
10 WEST 10/23/13 4:09 0.29719 0.1893 0.13493 250 500
11 WEST 10/23/13 4:26 0.29719 0.1893 0.13493 250 750
12 WEST 10/23/13 4:41 0.29719 0.1893 0.13493 -250 500
13 WEST 10/23/13 5:07 0.29719 0.1893 0.13493 -250 250
14 WEST 10/23/13 5:13 0.23345 0.19788 0.09304 -250 0
15 WEST 10/23/13 5:19 0.23345 0.19788 0.09304 250 250
16 WEST 10/23/13 5:23 0.29719 0.1893 0.13493 250 500
17 WEST 10/23/13 5:45 0.29719 0.1893 0.13493 250 750
For the calculated results I want need to ignore any record before the LAST RunningTotal of 0 (in the actual table for the same location the RunningTotal could zero out several times over the time span) and get a SumProduct for the X1,X2,X4 values with the final RunningTotal. The query would only use the values in yellow and the results would be :
Location SumProduct X1 SumProduct X2 SumProduct X3 Total Qty
EAST 0.26532 0.19359 0.11399 500
WEST 0.27594 0.19216 0.12097 750
Can you help me with tthe sql to get these results?
Sorry the yellow highlighting did not show. Basically for one location I need the values from the record after the LAST time the RunningTotal zeros out, although it may zero out many times before.
Respected Sir,
I have error on “php search engine useing sql” . Please tell me how do I create this ?
Are you use PHP is using SQL Server? What the exact error?
table
id vale
1 10 20
2 20 30
3 30 40
4 40 50
5 50 60
plz this question output
How to print these values in a single line like ‘1,2,3,4,5’?
instead of printing in loop, keep adding them in a variable. At the end of loop, use print.
thanks
Welcome Subbi.
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
select TOP (100000) * from [10.65.84.4].[CEAT].dbo.Curing_Trend with(NOLOCK) order by Rdatetime asc
OPEN @Mycursor
FETCH NEXT FROM @MyCursor
INTO @Rdatetime,@Barcode,@Cavity,@IPLH,@IPRH,@ITLH,@ITRH,@PTLH,@PTRH,@SPLH,@SPRH,@MachineID,@ShiftID,@C_Flag
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.Curing_Trend (Rdatetime, Barcode, Cavity, IPLH, IPRH, ITLH, ITRH, PTLH, PTRH, SPLH, SPRH, MachineID, ShiftID)
values(@Rdatetime,@Barcode,@Cavity,@IPLH,@IPRH,@ITLH,@ITRH,@PTLH,@PTRH,@SPLH,@SPRH,@MachineID,@ShiftID)
Update [10.65.84.4].[CEAT].dbo.Curing_Trend SET C_Flag = 0 where Rdatetime = @Rdatetime and Barcode = @Barcode
FETCH NEXT FROM @MyCursor
INTO @Rdatetime,@Barcode,@Cavity,@IPLH,@IPRH,@ITLH,@ITRH,@PTLH,@PTRH,@SPLH,@SPRH,@MachineID,@ShiftID,@C_Flag
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
Delete from [10.65.84.4].[CEAT].dbo.Curing_Trend where C_Flag =0
END TRY
hi @pinal dave
this is my Cursor loop to insert update and delete.
please can you suggest how can i use while loop because cursor takes too much time to execute
You need to find slow query in the loop. How many rows? and do you have right indexes? Looks like linked server query also involved. How is the query plan?
this is a 10 minute job activity which transfers 100000 records to another table in another server through the concept of linked server
Main issue is that i dont have indexes
Was that a solution?
No I need a quick way instead of cursor , Please help me out!!!
HI dave Please can you suggest any efficient methods?
Try bcp or SSIS.
Hi Pinal there is a strange requirement if you can provide your inputs and can achieve this in the SQL server. I have to compare the previous rows and find the delta for 100 around columns.So can we make it a procedure which inputs the column name from excel spreadsheet and loop around each and every field to provide the difference with the previous row?
this needs detailed understanding of table schema and won’t be possible via comments.
Hi. Is sql server 2005 and 2008 support while loop?
I am having employee table. Now i need to update a table through stored procedure where emp_id should 1000+ and if the emp_id is less than thousand it should be updated
ALTER PROCEDURE EMPLOYEE
AS
BEGIN
DECLARE @EMP_ID INT
SELECT @EMP_ID=EMP_ID FROM EMPLOYEE
UPDATE TABLE EMPLOYEE SET @EMP_ID=EMP_ID+1000
IF @EMP_ID<1000
UPDATE TABLE EMPLOYEE SET @EMP_ID=EMP_ID+1000
ELSE
UPDATE TABLE EMPLOYEE SET @EMP_ID=EMP_ID