August 6, 2002 at 5:03 am
How does a CURSOR work?
I was under the impression that:
1. the sql ran, the cursor was poulated
2. you did something with the value in the cursor
3. the sql ran and the cursor was poulated again.
But now I think the cursor holds the record set from the sql and you fetch one row at a time. I guess the record set is held in tempdb (if it is too big to hold in memory?)
-K
August 6, 2002 at 5:07 am
Depends on the cursor type. What you've described is a dynamic cursor. Other cursors vary. For example a keyset cursor only stores the key in tempdb, the other info is retrieved as needed.
Andy
August 6, 2002 at 5:12 am
Well, this is the exact cursor I was looking at.
select column_name,data_type from information_schema.columns where table_name ='central_regen_fact_table'
order by column_name
--------------------------------
CREATE PROCEDURE V02_CIM_DEN
(@BEGDATE VARCHAR(10),@ENDDATE VARCHAR(10),@CREATE AS VARCHAR(3) = 1, @TESTMODE AS VARCHAR(10) = 0)
AS
DECLARE @START_DOB_DATE DATETIME,
@END_DOB_DATE DATETIME,
@START_DATE DATETIME,
@TOTAL_COM_DAYS INT,
@MEDICAID INT
SELECT @NUM_BREAKS = 1,
@START_DOB_DATE = DATEADD(YY,-2,@BEGDATE),
@END_DOB_DATE = DATEADD(YY, -2,@ENDDATE),
@TOTAL_COM_DAYS = 0
BEGIN
DECLARE C_CECOUNT CURSOR FOR
SELECT E.MEMBER_ID, E.DATA_SOURCE, E.ELIG_BEG_DATE, E.ELIG_END_DATE,E.MED_COM, E.MED_COM_TYPE, E.MEMBER_DOB FROM ELIGIBILITY AS E
JOIN
(SELECT POP_LABEL, MEMBER_ID,DATA_SOURCE FROM V02_ASSOC_218_ENDING_MED_COM_TABLE) AS C1
ON E.MEMBER_ID = C1.MEMBER_ID
AND
E.DATA_SOURCE = C1.DATA_SOURCE
WHERE E.MEMBER_DOB >= @START_DOB_DATE AND E.MEMBER_DOB <= @END_DOB_DATE
AND E.ELIG_BEG_DATE <= DATEADD(YY,2,E.MEMBER_DOB)
AND E.ELIG_END_DATE >= DATEADD(YY, -1, (DATEADD(YY,2,E.MEMBER_DOB)))
AND C1.POP_LABEL IN ('MEDICAID','COMMERCIAL')
ORDER BY E.MEMBER_ID, E.DATA_SOURCE, E.ELIG_BEG_DATE
END
ELSE
BEGIN
DECLARE C_CECOUNT CURSOR FOR
SELECT E.MEMBER_ID, E.DATA_SOURCE, E.ELIG_BEG_DATE, E.ELIG_END_DATE,E.MED_COM, E.MED_COM_TYPE, E.MEMBER_DOB FROM ELIGIBILITY AS E
JOIN
(SELECT POP_LABEL, MEMBER_ID,DATA_SOURCE FROM V02_ASSOC_218_ENDING_MED_COM_TABLE) AS C1
ON E.MEMBER_ID = C1.MEMBER_ID
AND
E.DATA_SOURCE = C1.DATA_SOURCE
WHERE E.MEMBER_DOB >= @START_DOB_DATE AND E.MEMBER_DOB <= @END_DOB_DATE
AND E.ELIG_BEG_DATE <= DATEADD(YY,2,E.MEMBER_DOB)
AND E.ELIG_END_DATE >= DATEADD(YY, -1, (DATEADD(YY,2,E.MEMBER_DOB)))
AND C1.POP_LABEL IN ('MEDICAID','COMMERCIAL')
AND E.DATA_SOURCE = @TESTMODE
ORDER BY E.MEMBER_ID, E.DATA_SOURCE, E.ELIG_BEG_DATE
END
OPEN C_CECOUNT
FETCH NEXT FROM C_CECOUNT INTO @MEMBER_ID, @DATA_SOURCE, @ELIG_BEG_DATE, @ELIG_END_DATE, @MED_COM, @MED_COM_TYPE, @MEMBER_DOB
WHILE (@@FETCH_STATUS = 0)
BEGIN ---A OPEN MAIN CURSOR
SELECT @MANDELIG = DATEADD(YY,2,@MEMBER_DOB)
SELECT @START_DATE = DATEADD(YY, -1, (DATEADD(YY,2,@MEMBER_DOB)))
SELECT @END_DATE = DATEADD(YY,2,@MEMBER_DOB)
--CHECK IF THE MEMBER IS ELIGIBILE ON MANDATED ELIGBILITY DATE OR NOT?
--THIS HAS TO BE CHECKED FOR EACH ELIGIBILITY ROW FOR A MEMBER
IF (@MANDELIG BETWEEN CAST(@ELIG_BEG_DATE AS DATETIME)
AND CAST(@ELIG_END_DATE AS DATETIME))
BEGIN
SELECT @MANDELIGFOUND=1
END
--CHECK FOR PPO PRODUCT
IF @MED_COM = 'PPO'
BEGIN
SELECT @PPO_PRODUCT = 1
--KEEP TRACK OF TOTAL PPO DAYS
IF @START_DATE > @ELIG_BEG_DATE
BEGIN
IF @END_DATE < @ELIG_END_DATE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @START_DATE, @END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @START_DATE, @ELIG_END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
END
ELSE
BEGIN
IF @END_DATE < @ELIG_END_DATE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @ELIG_BEG_DATE, @END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @ELIG_BEG_DATE, @ELIG_END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
END
END
ELSE
BEGIN
SELECT @PPO_PRODUCT = 0
END
--CHECK FOR COMMERCIAL HMO OR POS PRODUCT
IF @MED_COM = 'COMMERCIAL'
BEGIN
SELECT @COM_PRODUCT = 1
--KEEP TRACK OF TOTAL COM DAYS
IF @START_DATE > @ELIG_BEG_DATE
BEGIN
IF @END_DATE < @ELIG_END_DATE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @START_DATE, @END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @START_DATE, @ELIG_END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
END
ELSE
BEGIN
IF @END_DATE < @ELIG_END_DATE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @ELIG_BEG_DATE, @END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @ELIG_BEG_DATE, @ELIG_END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
END
END
ELSE
BEGIN
SELECT @COM_PRODUCT = 0
END
--CHECK FOR MEDICAID PRODUCT
IF @MED_COM = 'MEDICAID'
BEGIN
SELECT @MEDICAID = 1
END
ELSE
BEGIN
SELECT @MEDICAID = 0
END
/* CHECK FOR BREAK AT BEGINNING */
SELECT @BREAK_DAYS = DATEDIFF(D, @START_DATE, @ELIG_BEG_DATE)
IF @BREAK_DAYS > 0
BEGIN
SELECT @BREAK_COUNT = @BREAK_COUNT + 1,@TOTAL_BREAK_DAYS=@TOTAL_BREAK_DAYS+@BREAK_DAYS
IF @BREAK_COUNT> @NUM_BREAKS OR @TOTAL_BREAK_DAYS > @NUM_DAYS
BEGIN
SELECT @CE_COUNT = 0
END
END
IF (@CE_COUNT != 0)
BEGIN
FETCH NEXT FROM C_CECOUNT INTO @NEXT_MEMBER_ID, @NEXT_DATA_SOURCE, @NEXT_ELIG_BEG_DATE, @NEXT_ELIG_END_DATE, @MED_COM, @MED_COM_TYPE, @NEXT_MEMBER_DOB
IF (@@FETCH_STATUS = 0)
BEGIN
WHILE @NEXT_MEMBER_ID = @MEMBER_ID AND @NEXT_DATA_SOURCE = @DATA_SOURCE
BEGIN
IF @MANDELIGFOUND<>1 AND (@MANDELIG BETWEEN CAST(@NEXT_ELIG_BEG_DATE AS DATETIME)
AND CAST(@NEXT_ELIG_END_DATE AS DATETIME))
BEGIN
SELECT @MANDELIGFOUND=1
END
IF @MED_COM = 'PPO'
BEGIN
SELECT @PPO_PRODUCT = 1
IF @START_DATE > @NEXT_ELIG_BEG_DATE
BEGIN
IF @END_DATE < @NEXT_ELIG_END_DATE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @START_DATE, @END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @START_DATE, @NEXT_ELIG_END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
END
ELSE
BEGIN
IF @END_DATE < @NEXT_ELIG_END_DATE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @NEXT_ELIG_BEG_DATE, @END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_PPO_DAYS = DATEDIFF(D, @NEXT_ELIG_BEG_DATE, @NEXT_ELIG_END_DATE) + 1,
@PPO_BREAK_COUNT = @PPO_BREAK_COUNT + 1
END
END
END
ELSE
BEGIN
SELECT @PPO_PRODUCT = 0
END
--CHECK FOR COMMERCIAL PRODUCT
IF @MED_COM = 'COMMERCIAL'
BEGIN
SELECT @COM_PRODUCT = 1
--KEEP TRACK OF TOTAL PPO DAYS
IF @START_DATE > @NEXT_ELIG_BEG_DATE
BEGIN
IF @END_DATE < @NEXT_ELIG_END_DATE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @START_DATE, @END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @START_DATE, @NEXT_ELIG_END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
END
ELSE
BEGIN
IF @END_DATE < @NEXT_ELIG_END_DATE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @NEXT_ELIG_BEG_DATE, @END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
ELSE
BEGIN
SELECT @TOTAL_COM_DAYS = DATEDIFF(D, @NEXT_ELIG_BEG_DATE, @NEXT_ELIG_END_DATE) + 1,
@COM_BREAK_COUNT = @COM_BREAK_COUNT + 1
END
END
END
ELSE
BEGIN
SELECT @COM_PRODUCT = 0
END
--CHECK FOR MEDICAID PRODUCT
IF @MED_COM = 'MEDICAID'
BEGIN
SELECT @MEDICAID = 1
END
ELSE
BEGIN
SELECT @MEDICAID = 0
END
SELECT @BREAK_DAYS = DATEDIFF(D, @ELIG_END_DATE, @NEXT_ELIG_BEG_DATE) - 1
IF @BREAK_DAYS > 0
BEGIN
SELECT @BREAK_COUNT = @BREAK_COUNT + 1,
@TOTAL_BREAK_DAYS=@TOTAL_BREAK_DAYS+@BREAK_DAYS
IF @BREAK_COUNT> @NUM_BREAKS OR @TOTAL_BREAK_DAYS > @NUM_DAYS
BEGIN
SELECT @CE_COUNT = 0
BREAK
END
END
SELECT @ELIG_END_DATE = @NEXT_ELIG_END_DATE
FETCH NEXT FROM C_CECOUNT INTO @NEXT_MEMBER_ID, @NEXT_DATA_SOURCE, @NEXT_ELIG_BEG_DATE,
@NEXT_ELIG_END_DATE, @MED_COM, @MED_COM_TYPE, @NEXT_MEMBER_DOB
IF (@@FETCH_STATUS != 0)
BREAK
ELSE
CONTINUE
END
END
/*NEXT RECORD IS DIFFERENT MEMBER OR END OF CURSOR, SO CHECK FOR BREAK AT END FOR CURRENT MEMBER*/
IF (@CE_COUNT != 0)
BEGIN
SELECT @BREAK_DAYS = DATEDIFF(D, @ELIG_END_DATE, @END_DATE)
IF @BREAK_DAYS > 0
BEGIN
SELECT @BREAK_COUNT = @BREAK_COUNT + 1,@TOTAL_BREAK_DAYS=@TOTAL_BREAK_DAYS+@BREAK_DAYS
IF @BREAK_COUNT> @NUM_BREAKS OR @TOTAL_BREAK_DAYS > @NUM_DAYS OR @MANDELIGFOUND=0
BEGIN
SELECT @CE_COUNT = 0
END
END
END
/* IF LAST PRODUCT TYPE IS NOT PPO, BUT THERE WAS A PREVIOUS PRODUCT TYPE OF PPO FOR
THIS MEMBER, THEN CONSIDER THE PPO DAYS AS A BREAK */
IF (@CE_COUNT != 0) AND (@PPO_PRODUCT = 0) AND
( (@TOTAL_PPO_DAYS + @TOTAL_BREAK_DAYS > @NUM_DAYS))
BEGIN
SELECT @CE_COUNT = 0
END
/* IF LAST PRODUCT TYPE IS PPO, BUT THERE WAS A PREVIOUS PRODUCT TYPE OF COMMERCIAL FOR
THIS MEMBER, THEN CONSIDER THE COM DAYS AS A BREAK */
IF (@CE_COUNT != 0) AND (@PPO_PRODUCT = 1) AND
( (@TOTAL_COM_DAYS + @TOTAL_BREAK_DAYS > @NUM_DAYS))
BEGIN
SELECT @CE_COUNT = 0
END
IF ((@CE_COUNT != 0) AND (@MANDELIGFOUND) = 1)
BEGIN
INSERT INTO HEDISUSER.V02_CIM_DEN_TABLE (MEMBER_ID, DATA_SOURCE, MEMBER_DOB) VALUES (@MEMBER_ID,@DATA_SOURCE,@MEMBER_DOB)
END
IF (@@FETCH_STATUS = 0)
BEGIN
SELECT @MEMBER_ID = @NEXT_MEMBER_ID,
@DATA_SOURCE = @NEXT_DATA_SOURCE,
@ELIG_BEG_DATE = @NEXT_ELIG_BEG_DATE,
@ELIG_END_DATE = @NEXT_ELIG_END_DATE,
@MEMBER_DOB = @NEXT_MEMBER_DOB,
@BREAK_COUNT = 0,@MANDELIGFOUND=0,
@TOTAL_BREAK_DAYS=0,
@CE_COUNT = 1,
@PPO_PRODUCT = 0,
@PPO_BREAK_COUNT = 0,
@TOTAL_PPO_DAYS = 0,
@COM_PRODUCT = 0,
@COM_BREAK_COUNT = 0,
@TOTAL_COM_DAYS = 0
END
END
ELSE /*NOT CONTINUOSLY ENROLLED, SO GET NEXT MEMBER*/
BEGIN
FETCH NEXT FROM C_CECOUNT INTO @NEXT_MEMBER_ID, @NEXT_DATA_SOURCE, @NEXT_ELIG_BEG_DATE, @NEXT_ELIG_END_DATE, @MED_COM, @MED_COM_TYPE, @NEXT_MEMBER_DOB
IF (@@FETCH_STATUS = 0)
BEGIN
WHILE @NEXT_MEMBER_ID = @MEMBER_ID AND @NEXT_DATA_SOURCE = @DATA_SOURCE
BEGIN
FETCH NEXT FROM C_CECOUNT INTO @NEXT_MEMBER_ID, @NEXT_DATA_SOURCE, @NEXT_ELIG_BEG_DATE, @NEXT_ELIG_END_DATE, @MED_COM, @MED_COM_TYPE, @NEXT_MEMBER_DOB
IF (@@FETCH_STATUS != 0)
BREAK
ELSE
CONTINUE
END
END
IF (@@FETCH_STATUS = 0)
BEGIN
SELECT @MEMBER_ID = @NEXT_MEMBER_ID,
@DATA_SOURCE = @NEXT_DATA_SOURCE,
@ELIG_BEG_DATE = @NEXT_ELIG_BEG_DATE,
@ELIG_END_DATE = @NEXT_ELIG_END_DATE,
@MEMBER_DOB = @NEXT_MEMBER_DOB,
@BREAK_COUNT = 0,@MANDELIGFOUND=0,
@TOTAL_BREAK_DAYS=0,
@CE_COUNT = 1,
@PPO_PRODUCT = 0,
@PPO_BREAK_COUNT = 0,
@TOTAL_PPO_DAYS = 0,
@COM_PRODUCT = 0,
@COM_BREAK_COUNT = 0,
@TOTAL_COM_DAYS = 0
END
END
END
DEALLOCATE C_CECOUNT
END
END
ELSE -- @create = 2 (HYBRID MODE)
BEGIN --INSERT INTO HYBRID DENOMINATOR TABLE THE MEMBERS IN SAMPLE
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'HYBRID_V02_CIM_DEN_TABLE' AND TABLE_SCHEMA='HEDISUSER' AND TABLE_TYPE = 'BASE TABLE')
TRUNCATE TABLE HEDISUSER.HYBRID_V02_CIM_DEN_TABLE
ELSE
CREATE TABLE HEDISUSER.HYBRID_V02_CIM_DEN_TABLE ( MEMBER_ID VARCHAR(80), DATA_SOURCE VARCHAR(10), MEMBER_DOB DATETIME)
INSERT INTO HEDISUSER.HYBRID_V02_CIM_DEN_TABLE
SELECT distinct A.MEMBER_ID, A.DATA_SOURCE, A.MEMBER_DOB
FROM V02_CIM_DEN_TABLE AS A
INNER JOIN
(SELECT DISTINCT MEMBER_ID FROM MEDQUEST_OUTPUT
WHERE ACTIONRS IN ('1','4')
AND MEASURE_NUMBER_MQ = 1) AS B
ON
A.MEMBER_ID = B.MEMBER_ID
END
GO
August 6, 2002 at 8:27 am
There are great explanations in Inside SQL Server for SQL 2000. Some are in tempdb, some are populated as you retrieve rows. Depends.
For your cursor, it is accepting default options, so it is forward only (cannot fetch previous), updatable (if you have permissions) and the data is read from the underlying tables. If someone updates a row that you have not read in the cursor, the update will appear in the cursor when you reach that row.
In BOL, the T-SQL Reference, look at the DECLARE CURSOR entry.
Steve Jones
August 6, 2002 at 8:37 am
SO it has to rerun the query for each row?
I will buy that book.
-Kevin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply