SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 do-while loop within cursor - running indefinitely
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

edyl
Starting Member

33 Posts

Posted - 10/11/2012 :  10:39:00  Show Profile  Reply with Quote
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

USA
15665 Posts

Posted - 10/11/2012 :  10:51:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
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)
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/11/2012 :  11:03:15  Show Profile  Reply with Quote
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
Go to Top of Page

edyl
Starting Member

33 Posts

Posted - 10/11/2012 :  18:13:55  Show Profile  Reply with Quote
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.
Go to Top of Page

edyl
Starting Member

33 Posts

Posted - 10/11/2012 :  18:15:26  Show Profile  Reply with Quote
Thanks Jim. That worked like a charm!

But its like magic to me! especially the part - "master..spt_values val
WHERE val.type = 'p'"
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1690 Posts

Posted - 10/11/2012 :  19:32:34  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 10/11/2012 19:34:18
Go to Top of Page

nandhujob88
Starting Member

India
4 Posts

Posted - 10/12/2012 :  03:15:17  Show Profile  Reply with Quote
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


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.







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
Go to Top of Page

edyl
Starting Member

33 Posts

Posted - 10/12/2012 :  09:31:11  Show Profile  Reply with Quote
Thanks Nandhu and Bustaz. that makes the cursor work.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000