September 13, 2016 at 4:27 am
hi
i am using the two cursors in one sp getting the error infinite loop. Please give me the solution to resolve this issue and i need desired output.
my table structures
--1--Scheduling tasks between from SCHEDULE_START_DATE to SCHEDULE_END_DATE
CREATE TABLE TASK_SCHEDULES(
CM_VITAL_TASK_SCHEDULE_ID bigint IDENTITY(1,1) NOT NULL,
VITAL_SIGN_ID int NULL,
PATIENT_ID bigint NULL,
SCHEDULE_TYPE int NULL,
SCHEDULE_START_DATE datetime NULL,
SCHEDULE_END_DATE datetime NULL,
SCHEDULE_TIME nvarchar(255) NULL,
SCHEDULE_FREQUENCY_TYPE int NULL,
SCHEDULE_FREQUENCY int NULL,
RE_SCHEDULE_DATE datetime NULL,
RE_SCHEDULED_BY bigint NULL,
REF_SCHEDULE_ID bigint NULL,
IS_CONFIRMED char(1) NULL,
FACILITY_ID int NULL,
CUSTOMER_ID int NULL,
GN_STATUS int NULL,
GN_MACHINE_IP varchar(20) NULL,
CREATED_BY bigint NULL,
CREATED_ON datetime NULL,
EDITED_BY bigint NULL,
EDITED_ON datetime NULL
)
GO
INSERT TASK_SCHEDULES(CM_VITAL_TASK_SCHEDULE_ID,VITAL_SIGN_ID,PATIENT_ID,SCHEDULE_TYPE,SCHEDULE_START_DATE,SCHEDULE_END_DATE,SCHEDULE_TIME,SCHEDULE_FREQUENCY_TYPE,SCHEDULE_FREQUENCY,RE_SCHEDULE_DATE,RE_SCHEDULED_BY,REF_SCHEDULE_ID,IS_CONFIRMED,FACILITY_ID,CUSTOMER_ID,GN_STATUS,GN_MACHINE_IP,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON)
VALUES('2','2002','191',NULL,convert(datetime,'2016-09-08 00:00:00.000',121),convert(datetime,'2016-09-16 00:00:00.000',121),NULL,'5135','5135',convert(datetime,NULL,121),NULL,NULL,'Y','3','2','2','127.0.0.1','1',convert(datetime,'2016-09-08 18:18:03.520',121),'1',convert(datetime,'2016-09-08 18:18:03.520',121))
---
---2-- Frequency master
CREATE TABLE DBO.SCHEDULE_FREQUENCY
(
SCHEDULE_FREQUENCY_ID INT NOT NULL IDENTITY(1,1),
FREQUENCY_NAME NVARCHAR(400) NULL,
FREQUENCY_TIME CHAR(5) NULL,
GN_STATUSint,
CREATED_BYbigint,
CREATED_ONdatetime,
EDITED_BYbigint,
EDITED_ONdatetime
)
GO
INSERT INTO SCHEDULE_FREQUENCY(FREQUENCY_NAME,FREQUENCY_TIME,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('BEFORE BREAKFAST','09:00',1,1,GETDATE(),1,GETDATE())
INSERT INTO SCHEDULE_FREQUENCY(FREQUENCY_NAME,FREQUENCY_TIME,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('BEFORE LUNCH','13:00',1,1,GETDATE(),1,GETDATE())
INSERT INTO SCHEDULE_FREQUENCY(FREQUENCY_NAME,FREQUENCY_TIME,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('BEFORE DINNER','21:00',1,1,GETDATE(),1,GETDATE())
INSERT INTO SCHEDULE_FREQUENCY(FREQUENCY_NAME,FREQUENCY_TIME,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('AFTER DINNER','21:01',1,1,GETDATE(),1,GETDATE())
--3--TASK_SCHEDULE_FREQ against CM_VITAL_TASK_SCHEDULE_ID from TASK_SCHEDULES table
CREATE TABLE TASK_SCHEDULE_FREQ(
CM_VITAL_TASK_SCHEDULE_ID BIGINT,
SCHEDULE_FREQUENCY_ID INT,
GN_STATUSint,
CREATED_BYbigint,
CREATED_ONdatetime,
EDITED_BYbigint,
EDITED_ONdatetime
)
GO
INSERT TASK_SCHEDULE_FREQ(CM_VITAL_TASK_SCHEDULE_ID,SCHEDULE_FREQUENCY_ID,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('2','1','1','1',convert(datetime,'2016-09-08 18:31:29.697',121),'1',convert(datetime,'2016-09-08 18:31:29.697',121))
INSERT TASK_SCHEDULE_FREQ(CM_VITAL_TASK_SCHEDULE_ID,SCHEDULE_FREQUENCY_ID,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('2','2','1','1',convert(datetime,'2016-09-08 18:31:29.713',121),'1',convert(datetime,'2016-09-08 18:31:29.713',121))
INSERT TASK_SCHEDULE_FREQ(CM_VITAL_TASK_SCHEDULE_ID,SCHEDULE_FREQUENCY_ID,GN_STATUS,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON) VALUES('2','3','1','1',convert(datetime,'2016-09-08 18:31:29.730',121),'1',convert(datetime,'2016-09-08 18:31:29.730',121))
GO
--4--My desired output need to store in this task events against schedule CM_VITAL_TASK_SCHEDULE_ID iterate data between from SCHEDULE_START_DATE to SCHEDULE_END_DATE
CREATE TABLE TASK_EVENTS(
CM_VITAL_TASK_EVENT_ID bigint IDENTITY(1,1) NOT NULL,
PATIENT_ID bigint NULL,
VITAL_SIGN_ID int NULL,
CM_VITAL_TASK_SCHEDULE_ID bigint NULL,
EVENT_DATE datetime NULL,
EVENT_TIME varchar(10) NULL,
EVENT_SLOT int NULL,
ASSIGNED_TO bigint NULL,
CM_TASK_EVENT_STATUS_ID int NULL,
PAT_VITAL_SIGN_ID bigint NULL,
IS_CONFIRMED char(1) NULL,
FACILITY_ID int NULL,
CUSTOMER_ID int NULL,
GN_STATUS int NULL,
GN_MACHINE_IP varchar(20) NULL,
CREATED_BY bigint NULL,
CREATED_ON datetime NULL,
EDITED_BY bigint NULL,
EDITED_ON datetime NULL,
EVENT_END_DATE datetime NULL,
EVENT_TYPE_CODE varchar(20) NULL
)
--5--for the output storing into task events written mystored proc
ALTER PROC [dbo].[USP_PAT_VITAL_SCHEDULER_INS_testing]
(
@PATIENT_ID bigint
)
AS
BEGIN
DECLARE @SCHSTARTDATE DATETIME
DECLARE @SCHENDDATE DATETIME
DECLARE @SCHFREQTP INT,@VITAL_SIGN_ID INT,@CM_VITAL_TASK_SCHEDULE_ID BIGINT
--DECLARE @SCHSTARTDATEITER DATETIME
DECLARE db_getTaskSchedule CURSOR FOR
SELECT SCHEDULE_START_DATE,SCHEDULE_END_DATE,SCHEDULE_FREQUENCY_TYPE
,VITAL_SIGN_ID ,TS.CM_VITAL_TASK_SCHEDULE_ID
FROM TASK_SCHEDULES TS
WHERE PATIENT_ID = @PATIENT_ID
OPEN db_getTaskSchedule
FETCH NEXT FROM db_getTaskSchedule INTO @SCHSTARTDATE,@SCHENDDATE,@SCHFREQTP,@VITAL_SIGN_ID
,@CM_VITAL_TASK_SCHEDULE_ID
WHILE @SCHENDDATE > @SCHSTARTDATE
BEGIN
DECLARE @FREQUENCY_TIME CHAR(5),@TSF_CM_VITAL_TASK_SCHEDULE_ID INT
DECLARE db_getTaskSchFreq CURSOR FOR
SELECT TSF.CM_VITAL_TASK_SCHEDULE_ID,FREQUENCY_TIME
FROM TASK_SCHEDULE_FREQ TSF
JOIN SCHEDULE_FREQUENCY SF ON SF.SCHEDULE_FREQUENCY_ID = TSF.SCHEDULE_FREQUENCY_ID
WHERE TSF.CM_VITAL_TASK_SCHEDULE_ID = @CM_VITAL_TASK_SCHEDULE_ID
OPEN db_getTaskSchFreq
FETCH NEXT FROM db_getTaskSchFreq INTO @TSF_CM_VITAL_TASK_SCHEDULE_ID,@FREQUENCY_TIME
WHILE @CM_VITAL_TASK_SCHEDULE_ID = @TSF_CM_VITAL_TASK_SCHEDULE_ID
BEGIN
PRINT @SCHSTARTDATE+1
--SELECT @SCHTIME,@SCHSTARTDATE,@SCHENDDATE,@SCHFREQTP,@VITAL_SIGN_ID,@CM_VITAL_TASK_SCHEDULE_ID
-- INSERT INTO TASK_EVENTS(PATIENT_ID,VITAL_SIGN_ID,CM_VITAL_TASK_SCHEDULE_ID,EVENT_DATE,EVENT_TIME,
--EVENT_SLOT,ASSIGNED_TO,CM_TASK_EVENT_STATUS_ID,PAT_VITAL_SIGN_ID,IS_CONFIRMED,FACILITY_ID,CUSTOMER_ID,GN_STATUS,
--GN_MACHINE_IP,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON,EVENT_END_DATE,EVENT_TYPE_CODE)
-- VALUES(@PATIENT_ID,@VITAL_SIGN_ID,@CM_VITAL_TASK_SCHEDULE_ID,@SCHSTARTDATE,@FREQUENCY_TIME,1,1,1,NULL,'Y',3,2,1,'127.0.0.1',
-- 1,GETDATE(),1,GETDATE(),@SCHSTARTDATE,1)
END
--------
CLOSE db_getTaskSchFreq
DEALLOCATE db_getTaskSchFreq
PRINT 1
FETCH NEXT FROM db_getTaskSchedule INTO @SCHSTARTDATE,@SCHENDDATE,@SCHFREQTP,@VITAL_SIGN_ID,@CM_VITAL_TASK_SCHEDULE_ID
SET @SCHSTARTDATE = DATEADD(DD,1,@SCHSTARTDATE)
END
CLOSE db_getTaskSchedule
DEALLOCATE db_getTaskSchedule
END
i am storing data into events table as a schedule startdate increments to schedule enddate with 3 frequencies through procedure.
my desired output into the table of task_events as -->
CM_VITAL_TASK_EVENT_IDPATIENT_IDVITAL_SIGN_IDCM_VITAL_TASK_SCHEDULE_IDEVENT_DATEEVENT_TIMEEVENT_SLOTASSIGNED_TOCM_TASK_EVENT_STATUS_IDPAT_VITAL_SIGN_IDIS_CONFIRMEDFACILITY_IDCUSTOMER_IDGN_STATUSGN_MACHINE_IPCREATED_BYCREATED_ONEDITED_BYEDITED_ONEVENT_END_DATEEVENT_TYPE_CODE
13191200222016-09-08 00:00:00.00009:00111NULLY321127.0.0.112016-09-13 18:37:53.56312016-09-13 18:37:53.5632016-09-08 00:00:00.0001
14191200222016-09-08 00:00:00.00013:00111NULLY321127.0.0.112016-09-13 18:37:53.56312016-09-13 18:37:53.5632016-09-08 00:00:00.0001
15191200222016-09-08 00:00:00.00021:00111NULLY321127.0.0.112016-09-13 18:37:53.56312016-09-13 18:37:53.5632016-09-08 00:00:00.0001
16191200222016-09-09 00:00:00.00009:00111NULLY321127.0.0.112016-09-13 18:37:53.56312016-09-13 18:37:53.5632016-09-09 00:00:00.0001
17191200222016-09-09 00:00:00.00013:00111NULLY321127.0.0.112016-09-13 18:37:53.56312016-09-13 18:37:53.5632016-09-09 00:00:00.0001
18191200222016-09-09 00:00:00.00021:00111NULLY321127.0.0.112016-09-13 18:37:53.56312016-09-13 18:37:53.5632016-09-09 00:00:00.0001
till my scheduled_end_date 2016-09-16 00:00:00.000
Thanks in advance
September 13, 2016 at 4:36 am
Looking very quickly, you have a part of your code as follows:
OPEN db_getTaskSchFreq
FETCH NEXT FROM db_getTaskSchFreq INTO @TSF_CM_VITAL_TASK_SCHEDULE_ID,@FREQUENCY_TIME
WHILE @CM_VITAL_TASK_SCHEDULE_ID = @TSF_CM_VITAL_TASK_SCHEDULE_ID
BEGIN
PRINT @SCHSTARTDATE+1
END
--------
CLOSE db_getTaskSchFreq
That while loop in clearly incomplete, as once that criteria is filled, it'll infinitely loop inside. Neither @CM_VITAL_TASK_SCHEDULE_ID or @TSF_CM_VITAL_TASK_SCHEDULE_ID values are altered inside the While.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 13, 2016 at 7:24 am
Lucky for you this didn't run or you might have used this in your actual production system. There is absolutely no need for all these loops here. This is an ideal situation to use a tally table instead of nested cursors like this. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
By the way, you did an awesome job posting ddl and sample data!!! I will be happy to help you turn this into a set based insert but I don't quite understand the business rules here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2016 at 8:40 am
I can see, sanjeeva, that you've edited your post after our two replies, but I'm not really sure what you've added. You're always best replying to people, rather than editing, after you've had some answers.
Editing won't notify anyone of your change, and unless you express emphasize what you changed, no one will have any idea what is you've added to your post.
Thanks.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 13, 2016 at 5:52 pm
If you must use a cursor, you need to think about whether a static cursor or a dynamic cursor (etc) would more explicitly suit the needs of business/logic. Transact-SQL cursors have additional nuances - see msdn's or technet's OLH. Both of the batch's FETCHes will reset your batch's @@FETCH_STATUS. The batch should be handling @@FETCH_STATUS after each fetch. Because the batch's FETCHes are nested, the batch needs to handle its one @@FETCH_STATUS even more carefully. When the inner loop fails to fetch an additional row, should the inner loop CONTINUE or BREAK?
While the batch is a start and reflects a considerable amount of labor/time. I recommend you spend at least the same amount of time, looking at alternatives to nested cursors. Cursors can easily be thousands of times slower than a set-based solution, and can easily consume thousands of times more resources than a set-based solution,
September 14, 2016 at 11:11 pm
Hi Thom,
I forgot to add my desired output, just added that part.!
Thanks for updating.
September 14, 2016 at 11:59 pm
Did you forget FETCH NEXT in the inner cursor?
OPEN db_getTaskSchFreq
FETCH NEXT FROM db_getTaskSchFreq INTO @TSF_CM_VITAL_TASK_SCHEDULE_ID, @FREQUENCY_TIME
WHILE @CM_VITAL_TASK_SCHEDULE_ID = @TSF_CM_VITAL_TASK_SCHEDULE_ID
BEGIN
PRINT @SCHSTARTDATE+1
--SELECT @SCHTIME,@SCHSTARTDATE,@SCHENDDATE,@SCHFREQTP,@VITAL_SIGN_ID,@CM_VITAL_TASK_SCHEDULE_ID
-- INSERT INTO TASK_EVENTS(PATIENT_ID,VITAL_SIGN_ID,CM_VITAL_TASK_SCHEDULE_ID,EVENT_DATE,EVENT_TIME,
--EVENT_SLOT,ASSIGNED_TO,CM_TASK_EVENT_STATUS_ID,PAT_VITAL_SIGN_ID,IS_CONFIRMED,FACILITY_ID,CUSTOMER_ID,GN_STATUS,
--GN_MACHINE_IP,CREATED_BY,CREATED_ON,EDITED_BY,EDITED_ON,EVENT_END_DATE,EVENT_TYPE_CODE)
-- VALUES(@PATIENT_ID,@VITAL_SIGN_ID,@CM_VITAL_TASK_SCHEDULE_ID,@SCHSTARTDATE,@FREQUENCY_TIME,1,1,1,NULL,'Y',3,2,1,'127.0.0.1',
-- 1,GETDATE(),1,GETDATE(),@SCHSTARTDATE,1)
FETCH NEXT FROM db_getTaskSchFreq INTO @TSF_CM_VITAL_TASK_SCHEDULE_ID, @FREQUENCY_TIME
END
_____________
Code for TallyGenerator
September 15, 2016 at 12:08 am
WHILE in that loop will never stop.
Value of @TSF_CM_VITAL_TASK_SCHEDULE_ID is coming from TSF.CM_VITAL_TASK_SCHEDULE_ID which according to the cursor query is equal to @CM_VITAL_TASK_SCHEDULE_ID:
SELECT TSF.CM_VITAL_TASK_SCHEDULE_ID,FREQUENCY_TIME
FROM TASK_SCHEDULE_FREQ TSF
JOIN SCHEDULE_FREQUENCY SF ON SF.SCHEDULE_FREQUENCY_ID = TSF.SCHEDULE_FREQUENCY_ID
WHERE TSF.CM_VITAL_TASK_SCHEDULE_ID = @CM_VITAL_TASK_SCHEDULE_ID
OPEN db_getTaskSchFreq
FETCH NEXT FROM db_getTaskSchFreq INTO @TSF_CM_VITAL_TASK_SCHEDULE_ID, @FREQUENCY_TIME
WHILE @CM_VITAL_TASK_SCHEDULE_ID = @TSF_CM_VITAL_TASK_SCHEDULE_ID
Why don't you use normal cursor check?
WHILE @@FETCH_STATUS = 0
_____________
Code for TallyGenerator
September 15, 2016 at 12:23 am
If you're not feeling like emotionally attached to cursors then this should work fro you:
declare @PATIENT_ID int = 191
SELECT PATIENT_ID,VITAL_SIGN_ID,TS.CM_VITAL_TASK_SCHEDULE_ID, DATEADD(dd, t.N, SCHEDULE_START_DATE) EventDate, FREQUENCY_TIME EventTime
FROM TASK_SCHEDULES TS
INNER JOIN dbo.Tally t ON n <= DATEDIFF(dd, SCHEDULE_START_DATE, SCHEDULE_END_DATE)
INNER JOIN TASK_SCHEDULE_FREQ TSF ON TSF.CM_VITAL_TASK_SCHEDULE_ID = TS.CM_VITAL_TASK_SCHEDULE_ID
JOIN SCHEDULE_FREQUENCY SF ON SF.SCHEDULE_FREQUENCY_ID = TSF.SCHEDULE_FREQUENCY_ID
WHERE PATIENT_ID = @PATIENT_ID
ORDER BY EventDate, EventTime
Tally table contains sequential INT numbers from 0 to whatever number you possibly need.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply