Need help in converting this cursor to a set based operation I have a stored procedure which runs as part of a report database refresh nightly which takes about 2 hours complete. The object B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2 has 8 million rows. Can you suggest how can I convert this cursor to a set based approach for better performance? Can a CTE be used?

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP] AS      
SET NOCOUNT ON        
DECLARE @SEQ SMALLINT,      
@CASEID DECIMAL,       
@ASSIGNED  NCHAR(30),      
@AUDITSTAMP DATETIME,      
@AUDITOPRID NCHAR(60),      
@STATUS NCHAR(10),      
@LASTCASEID DECIMAL,       
@PROVGRPID NCHAR(20)      
SET @SEQ = 1      
SET @LASTCASEID = @CASEID      

DROP TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP   

CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP (      
  SEQ_NUM SMALLINT,       
  CASE_ID DECIMAL,       
  ASSIGNED_TO NCHAR(30),       
  AUDIT_STAMP DATETIME,       
  AUDIT_OPRID NCHAR(60),      
  RC_STATUS NCHAR(10),      
  PROVIDER_GRP_ID NCHAR(20) )      
DECLARE AUDIT CURSOR FOR        
   SELECT CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID      
   FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2      

-- Open the cursor      
OPEN AUDIT      
FETCH NEXT      
   FROM AUDIT      
    INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID       
WHILE @@FETCH_STATUS = 0      

begin       
 INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP       
 SELECT @SEQ, @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID      

SET @LASTCASEID = @CASEID      
FETCH NEXT      
   FROM AUDIT      
    INTO @CASEID, @ASSIGNED, @AUDITSTAMP, @AUDITOPRID, @STATUS, @PROVGRPID      

SET @SEQ = CASE WHEN @CASEID &lt;&gt; @LASTCASEID THEN 1 WHEN @CASEID = @LASTCASEID THEN @SEQ + 1 END      
end      
-- Close and deallocate the cursor      
CLOSE AUDIT      
DEALLOCATE AUDIT
Why do you need to DROP and RECREATE the table each time?  Since you're doing a DROP-CREATE, it must already exist. So, outside of the SP, recreate it as follows:

CREATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP (      
  SEQ_NUM SMALLINT,       
  CASE_ID DECIMAL,       
  ASSIGNED_TO NCHAR(30),       
  AUDIT_STAMP DATETIME,       
  AUDIT_OPRID NCHAR(60),      
  RC_STATUS NCHAR(10),      
  PROVIDER_GRP_ID NCHAR(20),
  PRIMARY KEY(CASE_ID, SEQ_NUM)
); -- don't leave an 8 million record table in a heap!


Since all you need is merely to number the records within a CASE from 1 to N, you can use ROW_NUMBER().

ALTER PROCEDURE [dbo].[B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP2_SP]
AS
  SET NOCOUNT ON;
  TRUNCATE TABLE B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP;

  INSERT INTO B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP       
  SELECT ROW_NUMBER() OVER (PARTITION BY CASE_ID ORDER BY AUDIT_STAMP),
         CASE_ID, ASSIGNED_TO, AUDIT_STAMP, AUDIT_OPRID, RC_STATUS, PROVIDER_GRP_ID      
    FROM B_RPT_RC_AUDIT_ASSIGNED_TO_TEMP_T2;
GO