# Is it necessary to replace this cursor in SQL Server 2005?

The Problem

I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a temp table and then assigning each of those notes a sequential value into the field NOTE_COUNTER (int) of the temp table. Here is the original code for it with the variables:

@lUPI_current          CHAR(32),
@lCASE_SEQ_current     INT,
@lDIFF_ENCOUNTER_DATE  INT,
@lcounter              INT

SET @lUPI_current = ''
SET @lCASE_SEQ_current = ''
SET @lDIFF_ENCOUNTER_DATE = 0
SET @lcounter = 0

DECLARE CURSOR_NOTE_COUNTER CURSOR FOR
SELECT
MEMBER,
CASE_SEQ,
ENCOUNTER_DATE
FROM
#TMP_NOTES
WHERE
TOTAL_CALLS > 1
ORDER BY
MEMBER,
CASE_SEQ,
ENCOUNTER_DATE

OPEN CURSOR_NOTE_COUNTER

FETCH NEXT FROM CURSOR_NOTE_COUNTER
INTO
@lUPI_current,
@lCASE_SEQ_current,
@lDIFF_ENCOUNTER_DATE

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @lcounter = (
SELECT
MAX(NOTE_COUNTER)
FROM
#TMP_NOTES
WHERE
MEMBER_UPI = @lUPI_current
AND CASE_SEQ = @lCASE_SEQ_current
)
UPDATE #TMP_NOTES
SET
NOTE_COUNTER = @lcounter + 1
FROM #TMP_NOTES
WHERE
MEMBER_UPI = @lUPI_current
AND CASE_SEQ = @lCASE_SEQ_current
AND DIFF_ENCOUNTER_DATE = @lDIFF_ENCOUNTER_DATE

FETCH NEXT FROM CURSOR_NOTE_COUNTER
INTO
@lUPI_current,
@lCASE_SEQ_current,
@lDIFF_ENCOUNTER_DATE
END


What I Have Tried

I tried replacing this with a CTE because of the notion that cursors should be avoided in favor of working on sets of data. Here was my attempt at that, the stumbling block being the ambiguity of the column names since it is working on the same table.

WITH RecordsBeingUpdated AS
(
SELECT
MEMBER_UPI,
CASE_SEQ,
DIFF_ENCOUNTER_DATE,
ROW_NUMBER() OVER (PARTITION BY MEMBER_UPI, CASE_SEQ, DIFF_ENCOUNTER_DATE ORDER BY DIFF_ENCOUNTER_DATE ASC) AS RowNum
FROM
#TMP_NOTES
WHERE
TOTAL_CALLS > 1
)

UPDATE #TMP_NOTES
SET
NOTE_COUNTER = R.RowNum
FROM
RecordsBeingUpdated AS R
WHERE
MEMBER_UPI = R.MEMBER_UPI
AND CASE_SEQ = R.CASE_SEQ
AND DIFF_ENCOUNTER_DATE = R.DIFF_ENCOUNTER_DATE


Please tell me if there is a cleaner way to doing this. I am grateful for any time that the community can provide.

• Is it possible for there to be multiple records keyed by MEMBER_UPI, CASE_SEQ, DIFF_ENCOUNTER_DATE? Just making sure I understand the requirements correctly. So, you would like to see NOTE_COUNTER increment within a grouping of M_U, C_S, and D_E_D, then restart for a new value. So you might have a sequence in NOTE_COUNTER like 1,2,3,1,1,1,2,3,4,... Oct 3 '12 at 19:22
• If you are concerned about column ambiguity between #TMP_NOTES and RecordsBeingUpdated, you could alias the columns in RecordsBeingUpdated. (e.g.: SELECT MEMBER_UPI AS MU, ...) Oct 3 '12 at 19:27
• In short, yes there may be multiple values. What I'm targeting is to take all of the CASE_SEQ values of each MEMBER_UPI and number them sequentially based off of the DIFF_ENCOUNTER_DATE. When there is a new CASE_SEQ for the same MEMBER_UPI, the numbering for NOTE_COUNTER should commence anew. Oct 3 '12 at 19:31
• In that case, shouldn't the DIFF_ENCOUNTER_DATE not be in the partition? Oct 3 '12 at 19:47
• @Griffin - Wow, really not one of my brighter moments haha. Much obliged, taking D_E_D out of the Partition worked. If you post as an answer and I'll accept. Oct 3 '12 at 19:49

So, you can simplify the CTE with some SQL magic:

WITH RecordsBeingUpdated AS
(
SELECT
NOTE_COUNTER,
MEMBER_UPI,
CASE_SEQ,
DIFF_ENCOUNTER_DATE,
ROW_NUMBER() OVER (PARTITION BY MEMBER_UPI, CASE_SEQ ORDER BY DIFF_ENCOUNTER_DATE ASC) AS RowNum
FROM
#TMP_NOTES
WHERE
TOTAL_CALLS > 1
)

UPDATE RecordsBeingUpdated
SET    NOTE_COUNTER = RowNum


Essentially, the virtual table built for the CTE has direct reference back to the concrete tables in the selection. This means you can update the CTE directly and SQL will update the corresponding concrete table within, provided the target table is the FROM table (not a joined) and there is no chance of ambiguity in what needs to be updated.

(Removed DIFF_ENCOUNTER_DATE from partition per comments).

• Many thanks for the thorough explanation, would up-vote if I were able to. Oct 3 '12 at 19:54
• Thank you. Also, to answer your title question - yes, please replace all cursors when possible. :) Oct 3 '12 at 19:57