I need to run a stored procedure on multiple records

Question

I need to run a stored procedure on a bunch of records. The code I have now iterates through the record stored in a temp table. The stored procedure returns a table of records.

I was wondering what I can do to avoid the iteration if anything.

set @counter = 1     
set @empnum = null     
set @lname = null     
set @fname = null     
-- get all punches for employees     
while exists(select emp_num, lname, fname from #tt_employees where id = @counter)     
begin     
    set @empnum = 0     
    select @empnum = emp_num, @lname = lname , @fname= fname from #tt_employees where id = @counter     

   INSERT @tt_hrs     
   exec PCT_GetEmpTimeSp 
      empnum    
     ,@d_start_dt     
     ,@d_end_dt     
     ,@pMode = 0    
     ,@pLunchMode = 3    
     ,@pShowdetail = 0    
     ,@pGetAll = 1          

  set @counter = @counter + 1     
end 

Answer 1

I think you are on the right track. you can have a temp table with identity column

CREATE TABLE #A (ID INT IDENTITY(1,1) NOT NULL, Name VARCHAR(50))

After records are inserted in to this temp table, find the total number of records in the table.

DECLARE @TableLength INTEGER
SELECT @TableLength  = MAX(ID) FROM #A

DECLARE @Index INT
SET @Index = 1

WHILE (@Index <=@TableLength)
BEGIN

-- DO your work here 

SET @Index = @Index + 1


END

Similar to what you have already proposed. Alternative to iterate over records is to use CURSOR. CURSORS should be avoided at any cost.

Answer 2

One way to avoid this kind of iteration is to analyze the code within the stored procedure and revised so that, rather than processing for one set of inputs at a time, it processes for all sets of inputs at a time. Often enough, this is not possible, which is why iteration loops are not all that uncommon.

A possible alternative is to use APPLY functionality (cross apply, outer apply). To do this, you'd rewrite the procedure as one of the table-type functions, and work that function into the query something like so:

INSERT @tt_hrs      
 select [columnList]
 from #tt_employees
  cross apply dbo.PCT_GetEmpTimeFunc(emp_num, @d_start_dt, @d_end_dt, 0, 3, 0, 1)

(It was not clear where all your inputs to the procedure were coming from.)

Note that you still are iterating over calls to the function, but now it's "packed" into one query.