do-while loop within cursor - running indefinitely

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

do-while loop within cursor - running indefinitely

Author

Topic

edyl
Starting Member

35 Posts

Posted - 2012-10-11 : 10:39:00

Hello Everyone,

I created a procedure which has a DO-WHILE Loop within a cursor. While executing it it runs for indefinitely. It looks like it doesnot come out of the loop. I have a table called ORDER where the to 10 rows are follows.

date			order_num	location	Start_Time	Stop_Time	start_time_in_mins	stop_time_in_mins	elapsed_time
2012-10-09 00:00:00	725499		ILL		08:10		09:30		490				570		1
2012-10-09 00:00:00	724459		UTH		08:11		10:25		491				625		2
2012-10-09 00:00:00	723841		UTH		08:12		09:00		492				540		1
2012-10-09 00:00:00	720637		UTH		08:23		09:35		503				575		1
2012-10-09 00:00:00	725469		UTH		08:24		08:40		504				520		0
2012-10-09 00:00:00	724357		UTH		08:30		09:35		510				575		1
2012-10-09 00:00:00	717136		ILL		08:31		09:11		511				551		1
2012-10-09 00:00:00	723721		UTH		08:40		10:00		520				600		2
2012-10-09 00:00:00	724727		UTH		08:58		09:53		538				593		1
2012-10-09 00:00:00	719931		ILL		09:01		10:47		541				647		1

I need to insert values into a NEW_TABLE with a row for each hour that elapsed between Start_Time and Stop_Time. Lets say in the case of Order_Num = 724459 I want

date			order_num	location	Start_Time	Stop_Time	Time_Intv	Row_Count			
2012-10-09 00:00:00	724459		UTH		08:11		10:25		8		1
2012-10-09 00:00:00	724459		UTH		08:11		10:25		9		1			
2012-10-09 00:00:00	724459		UTH		08:11		10:25		10		1

For that I wrote a procedure:

create PROCEDURE my_proc
AS
BEGIN

DECLARE @Date smalldatetime, @Order_Num varchar(50), @Location varchar(50), @Start_Time varchar(10), @Stop_Time varchar(10);
DECLARE @cel_start int, @cel_stop int, @cel_diff int;
DECLARE @i int;

DECLARE my_cur cursor
	FOR SELECT Date, Order_Num, Location, Start_Time, Stop_Time, CEILING (Start_Time_Val/60), CEILING (Stop_Time_Val/60)
		FROM Order;

OPEN my_cur;
FETCH NEXT FROM my_cur
INTO @Date, @Order_Num, @Location, @Start_Time, @Stop_Time, @cel_start, @cel_stop;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cel_diff = @cel_stop-@cel_start;
	SET @i = 0;
	WHILE @i <= @cel_diff
	BEGIN
		INSERT INTO my_new_table (Date, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
		VALUES (@Date, @Order_Num, @Location, @Start_Time, @Stop_Time,1, @cel_start+@i);
		SET @i = @i + 1;
	END;
END;
CLOSE my_cur;
DEALLOCATE my_cur;

END;
GO

But when I execute the procedure it runs indefinitely and keeps populating the data for the first row. What I am I missing. Please help.

Any recomedation, suggestions will be greatly appreciated.

Thanks in Advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-10-11 : 10:51:04

Why are you even using a cursor?

create PROCEDURE my_proc AS
SET NOCOUNT ON
;WITH n(n) AS (SELECT 0 UNION ALL SELECT n+1 FROM n)
INSERT INTO my_new_table (DATE, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
SELECT DATE, Order_Num, Location, Start_Time, Stop_Time, CEILING(Start_Time_Val/60)+n
	FROM [ORDER]
	CROSS JOIN n
	WHERE n<=CEILING(Stop_Time_Val/60)-CEILING(Start_Time_Val/60)
	OPTION (MAXRECURSION 0)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-11 : 11:03:15

Or this. A little less math involved

CREATE TABLE #temp(Date datetime,ordernum int,location char(3),start_time time,stop_time time
, start_time_in_mins int, stop_time_in_mins int,elapsed_time int
)

INSERT INTO #temp
VALUES
('2012-10-09 00:00:00',25499,'ILL','08:10','09:30', 490, 570, 1),
('2012-10-09 00:00:00',724459,'UTH','08:11','10:25', 491, 625, 2),
('2012-10-09 00:00:00',723841,'UTH','08:12','09:00', 492, 540, 1),
('2012-10-09 00:00:00',720637,'UTH','08:23','09:35', 503, 575, 1),
('2012-10-09 00:00:00',725469,'UTH','08:24','08:40', 504, 520, 0),
('2012-10-09 00:00:00',724357,'UTH','08:30','09:35', 510, 575, 1),
('2012-10-09 00:00:00',717136,'ILL','08:31','09:11', 511, 551, 1),
('2012-10-09 00:00:00',723721,'UTH','08:40','10:00', 520, 600, 2),
('2012-10-09 00:00:00',724727,'UTH','08:58','09:53', 538, 593, 1),
('2012-10-09 00:00:00',719931,'ILL','09:01','10:47', 541, 647, 1)

select t.*
,DATEPART(hh,start_time)+val.number as Time_Interval
from #temp t
CROSS JOIN master..spt_values val
WHERE val.type = 'p'
and val.number <= DATEDIFF(hh,start_time,stop_time)

Jim

Everyday I learn something that somebody else already knew

edyl
Starting Member

35 Posts

Posted - 2012-10-11 : 18:13:55

Thanks Robvolk.

I tried that, but that ran for about 3 minutes. The feeding table does not have more than 100 rows so I thought that it was taking too long and I had to cancel it.

edyl
Starting Member

35 Posts

Posted - 2012-10-11 : 18:15:26

Thanks Jim. That worked like a charm!

But its like magic to me!

especially the part - "master..spt_values val
WHERE val.type = 'p'"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-11 : 19:32:34

The set based approach given already is the way to go BUT, just for future reference, you are not performing a FETCH within the loop so only the first status is evaluated. Having said that, never use a cursor again unless there is a gun to your head.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

nandhujob88
Starting Member

4 Posts

Posted - 2012-10-12 : 03:15:17

quote:

Originally posted by edyl

Hello Everyone,

I created a procedure which has a DO-WHILE Loop within a cursor. While executing it it runs for indefinitely. It looks like it doesnot come out of the loop. I have a table called ORDER where the to 10 rows are follows.

date			order_num	location	Start_Time	Stop_Time	start_time_in_mins	stop_time_in_mins	elapsed_time
2012-10-09 00:00:00	725499		ILL		08:10		09:30		490				570		1
2012-10-09 00:00:00	724459		UTH		08:11		10:25		491				625		2
2012-10-09 00:00:00	723841		UTH		08:12		09:00		492				540		1
2012-10-09 00:00:00	720637		UTH		08:23		09:35		503				575		1
2012-10-09 00:00:00	725469		UTH		08:24		08:40		504				520		0
2012-10-09 00:00:00	724357		UTH		08:30		09:35		510				575		1
2012-10-09 00:00:00	717136		ILL		08:31		09:11		511				551		1
2012-10-09 00:00:00	723721		UTH		08:40		10:00		520				600		2
2012-10-09 00:00:00	724727		UTH		08:58		09:53		538				593		1
2012-10-09 00:00:00	719931		ILL		09:01		10:47		541				647		1

I need to insert values into a NEW_TABLE with a row for each hour that elapsed between Start_Time and Stop_Time. Lets say in the case of Order_Num = 724459 I want

date			order_num	location	Start_Time	Stop_Time	Time_Intv	Row_Count			
2012-10-09 00:00:00	724459		UTH		08:11		10:25		8		1
2012-10-09 00:00:00	724459		UTH		08:11		10:25		9		1			
2012-10-09 00:00:00	724459		UTH		08:11		10:25		10		1

For that I wrote a procedure:

create PROCEDURE my_proc
AS
BEGIN

DECLARE @Date smalldatetime, @Order_Num varchar(50), @Location varchar(50), @Start_Time varchar(10), @Stop_Time varchar(10);
DECLARE @cel_start int, @cel_stop int, @cel_diff int;
DECLARE @i int;

DECLARE my_cur cursor
	FOR SELECT Date, Order_Num, Location, Start_Time, Stop_Time, CEILING (Start_Time_Val/60), CEILING (Stop_Time_Val/60)
		FROM Order;

OPEN my_cur;
FETCH NEXT FROM my_cur
INTO @Date, @Order_Num, @Location, @Start_Time, @Stop_Time, @cel_start, @cel_stop;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cel_diff = @cel_stop-@cel_start;
	SET @i = 0;
	WHILE @i <= @cel_diff
	BEGIN
		INSERT INTO my_new_table (Date, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
		VALUES (@Date, @Order_Num, @Location, @Start_Time, @Stop_Time,1, @cel_start+@i);
		SET @i = @i + 1;
	END;
END;
CLOSE my_cur;
DEALLOCATE my_cur;

END;
GO

Hi edyl,

The reason for infinite loop is you was missed to include one more fetch statement before first begin's end statement

---------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cel_diff = @cel_stop-@cel_start;
SET @i = 0;
WHILE @i <= @cel_diff
BEGIN
INSERT INTO my_new_table (Date, Order_Num, Location, Start_Time, Stop_Time, Time_Intv)
VALUES (@Date, @Order_Num, @Location, @Start_Time, @Stop_Time,1, @cel_start+@i);
SET @i = @i + 1;
END;

FETCH NEXT FROM my_cur
INTO @Date, @Order_Num, @Location, @Start_Time, @Stop_Time, @cel_start, @cel_stop;

END;
------------------------------------------------------------

Nandhu - Software Analyst

edyl
Starting Member

35 Posts

Posted - 2012-10-12 : 09:31:11

Thanks Nandhu and Bustaz. that makes the cursor work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources