SQL IF structure...would like to convert to Case Statement

and remove the last comma in the

conf_count =   CASE WHEN @status_type = 14 THEN @row_count ELSE ors.conf_count END

Hello All, thanks so much for your quick response.  I've went with the If else structure as the case statements did not work in the stored proc i was using.  It looks like Wizilling  was the first to offer that suggestion so i will be rewarding the points to them.

Also, I was curious if anyone else had any last bit tips for speeding up the query.  I didn't really notice a dramatic speed increase with the if else..if structure.

It's a pretty large table that is being processed with a cursor.  

>>It's a pretty large table that is being processed with a cursor.  <<
Get rid of the CURSOR.

You know I was going to say the same thing... but sometimes I just get tired of banging that drum. :-)

I know the feeling.  This is somewhat akin to someone who asks: How do I stop my head hurting when I keep hitting it against the wall?

have you got the right index. And have u done a reindex lately. should help i think

Hey All, believe me, i would like to ditch the cursor...haven't found a way to do it yet.  Is there a way to get a cursor to run simultaneously?  Meaning, I'm processing a table that's say 300 records, split the table up into 3 sets of 100?  And then have the cursors run at the same time?

Let me know what you all think....

Post the cursor, we'll see what we can do.

It is a VERY bad thing to try to run the cursors concurrently. Do you understand the principles of blocking?

Yes, however i'm not worried about blocking because this table is for reporting only...it will not be receiving updates aside from a single DTS package that will run against it.

ALTER PROCEDURE [dbo].[ORS_RPMS_load_2]              
as
----------------------------------------------------------------------------------------------------------
--VARIABLE DECLARATIONS      
declare @status_type_id int,              
        @location_id int,              
        @status int,              
        @dte datetime,          
        @employee_id int    
------------------------------------------------------------------------------------------------------------
--BEGIN TEMP TABLE CREATION FOR CURRENT EMPLOYEES
select @dte = getdate()
drop table #employee
select employee_id, default_location_id,
0 as awl_count,
0 as warr_count,
0 as  dsch_count,
0 as parole_count,
0 as cbop_count,
0 as inst_count,
0 as aol_count,
0 as awol_count,
0 as afp_count,
0 as tarr_count,
0 as rev_count,
0 as parr_count,
0 as guest_count,
0 as conf_count,
@dte as last_update_date into #employee
from employee
where active_flag = 1 and    
employee_type_id in (7474,0)  

insert ORS_RPMS
select * from #employee
--END OF TEMP TABLE CREATION FOR CURRENT EMPLOYEES
------------------------------------------------------------------------------------------------------------
         
--deallocate status_types            
declare status_types cursor FORWARD_ONLY for              
select status_type_id from STATUS_TYPES
         
for read only              
--deallocate employee          
declare employee cursor FORWARD_ONLY for              
select employee_id from #employee
         
open employee      
 fetch NEXT from employee into @employee_id            

WHILE @@FETCH_STATUS = 0        
begin  

 print @employee_id        
 if @@FETCH_STATUS != 0            
       break                  
                             
    open status_types              
while @@fetch_status = 0
begin      
fetch next from  status_types into @status_type_id    
                 
                 
        --print @status_type_id  
 
        exec @status = ORS_RPMS_populate @employee_id, @status_type_id, @dte              
                 
    end                
             
    close status_types    
fetch NEXT from employee into @employee_id                  
end          
             
deallocate status_types              
deallocate employee          
         
return
GO

This is the process that it's calling from within the cursor:

ALTER proc [dbo].[ORS_RPMS_populate]        
    @employee_id        Int,        
    @status_type        Int,
      @dte                DateTime        
 AS          
       
declare @row_count int, @zero_rows char(1)      
     
SELECT @row_count = count(*)  
FROM    OFFENDER_STATUS ,    
   EMPLOYEE_BOOKING_ASSIGNMENT ,    
    EMPLOYEE ,    
    OFFENDER_BOOKING    
WHERE OFFENDER_STATUS.offender_booking_id = EMPLOYEE_BOOKING_ASSIGNMENT.offender_booking_id and    
    EMPLOYEE.employee_id = EMPLOYEE_BOOKING_ASSIGNMENT.employee_id and  
    OFFENDER_BOOKING.offender_booking_id = EMPLOYEE_BOOKING_ASSIGNMENT.offender_booking_id and      
    OFFENDER_STATUS.status_type_id = @status_type and      
    EMPLOYEE_BOOKING_ASSIGNMENT.employee_id = @employee_id and    
    OFFENDER_STATUS.next_status_id is Null and      
    EMPLOYEE.active_flag = 1 and      
    OFFENDER_BOOKING.active_flag = 1 and    
    EMPLOYEE_BOOKING_ASSIGNMENT.active_flag = 1    
       
If @@rowcount = 0      
begin      
    select @row_count = 0      
end      
       
If @status_type = 1      
begin      
    update ORS_RPMS set awl_count = @row_count where employee_id = @employee_id and last_update_date = @dte    
end      
     
else if @status_type = 2      
begin      
update ORS_RPMS set warr_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
       
else if  @status_type = 3      
begin      
    update ORS_RPMS set dsch_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
       
else if  @status_type = 4      
begin      
    update ORS_RPMS set parole_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
       
else if  @status_type = 5      
begin      
    update ORS_RPMS set cbop_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
else if  @status_type = 6      
begin      
    update ORS_RPMS set inst_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
       
else if  @status_type = 7      
begin      
   update ORS_RPMS set aol_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
else if  @status_type = 8  
begin      
  update ORS_RPMS set awol_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
else if  @status_type = 9      
begin      
    update ORS_RPMS set afp_count = @row_count where employee_id =@employee_id and last_update_date = @dte      
end        
     
else if  @status_type = 10      
begin      
    update ORS_RPMS set tarr_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
else if  @status_type= 11    
begin      
    update ORS_RPMS set rev_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end      
     
else if  @status_type = 12      
begin      
    update ORS_RPMS set parr_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
else if  @status_type = 13      
begin      
    update ORS_RPMS set guest_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
else if  @status_type = 14      
begin      
    update ORS_RPMS set conf_count = @row_count where employee_id = @employee_id and last_update_date = @dte      
end        
     
return
GO

I've opened a new question for the help in re-writing the procedure with the cursors...
https://www.experts-exchange.com/questions/22089154/Cursor-code-replacement.html