3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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,... \$\endgroup\$ – Griffin Oct 3 '12 at 19:22
  • \$\begingroup\$ 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, ...) \$\endgroup\$ – Griffin Oct 3 '12 at 19:27
  • \$\begingroup\$ 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. \$\endgroup\$ – Matt Susevich Oct 3 '12 at 19:31
  • \$\begingroup\$ In that case, shouldn't the DIFF_ENCOUNTER_DATE not be in the partition? \$\endgroup\$ – Griffin Oct 3 '12 at 19:47
  • \$\begingroup\$ @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. \$\endgroup\$ – Matt Susevich Oct 3 '12 at 19:49
3
\$\begingroup\$

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).

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.