title: Using loop to insert multiple row in SQL Server 2008?
body: <p>I have a temp table to store some data for calculating payroll. Data is related to many tables and is a long query logic. </p>
  
  <p>This stored procedure is to get some data from other tables then insert to <code>PayrollTemp</code> table for later use. It needs 3 parameters: the start and end date, and the <code>EmployeeID</code> (EmpID) for the main query and the nested queries. </p>
  
  <p>So whenever we run this stored procedure, we only get </p>
  
  <blockquote>
    <p>(1 row(s) affected)</p>
  </blockquote>
  
  <p>My question is how to use loop in SQL Server to pass every <code>EmployeeID</code> (EmpID) sequentially to store procedure in order to insert every record into the <code>PayrollTemp</code> table.</p>
  
  <pre><code>Create PROCEDURE [dbo].[sp_PayrollTemp_Init]
  @startDate date, @endDate date, @EmpID int
  
  AS
  
  INSERT INTO dbo.PayrollTemp([EmpID],[BasicSalary],[Allowance],[W_Hrs],[W_days],[OT150Hrs],[OT200Hrs],[OT300Hrs],[Night130Hrs],
  [NightOT150Hrs],[NightOT200Hrs],[NightOT300Hrs],[WeekdayDutyDays],[WeekendDutyDays],[OtherEarning],[SI],[HI],[UI],[UnionFee],[DependentDeduction],[OtherDeduction])
          select e.Id,e.BasicSalary,e.Allowance,
          (select SUM(ts.NormalHours) from TimeSheetA ts where EmpID=@EmpID)W_Hrs,
          (SELECT
             (DATEDIFF(dd, @startDate, @endDate) + 1)
            -(DATEDIFF(wk, @startDate, @endDate) * 2)
            -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday' THEN 1 ELSE 0 END)
            -(CASE WHEN DATENAME(dw, @endDate) = 'Saturday' THEN 1 ELSE 0 END))W_days,
          (select SUM(ts.OT150Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)OT150Hrs,
          (select SUM(ts.OT200Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)OT200Hrs,
          (select SUM(ts.OT300Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)OT300Hrs,
          (select SUM(ts.Night130Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)Night130Hrs,
          (select SUM(ts.NightOT150Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)NightOT150Hrs,
          (select SUM(ts.NightOT200Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)NightOT200Hrs,
          (select SUM(ts.NightOT300Hrs) from TimeSheetA ts where EmpID=@EmpID and TimeSheetDate between @startDate and @endDate)NightOT300Hrs,
  
          (select count(EmpID)WeekdayDuty from NightDutySchedule where DATENAME(DW,DutyDate) not in('Saturday','Sunday') and DutyDate between @startDate and @endDate and EmpID=@EmpID)WeekdayDutyDays,
          (select COUNT(EmpID) from NightDutySchedule where DATENAME(DW,DutyDate)='Saturday' or DATENAME(DW,DutyDate)='Sunday' and DutyDate between @startDate and @endDate and EmpID=@EmpID)WeekendDutyDays,
          (select SUM(EarningAmount) from EarningDetails where EarningDate between @startDate and @endDate and EmpID=@EmpID)OtherEarning,
          (SELECT case e.Is_Probation
              WHEN  'False' THEN
              e.BasicSalary*0.08
              ELSE 0 end 
            )SI,
            (SELECT case e.Is_Probation
              WHEN  'False' THEN
              e.BasicSalary*0.015
              ELSE 0 end 
            )HI,
            (SELECT case e.Is_Probation
              WHEN  'False' THEN
              e.BasicSalary*0.01
              ELSE 0 end 
            )UI,
            (SELECT case e.Is_Probation
              WHEN  'False' THEN
              case  when e.BasicSalary &gt; 4000000 or e.BasicSalary = 4000000 then  20000   else 15000 end
              ELSE 0 end 
            )UnionFee,
            (SELECT case e.Is_Probation
              WHEN  'False' THEN
                  e.Dependants * 3600000
              ELSE 0 end 
            )DependentDeduction,
           (select SUM(DeductionAmount) from DeductionDetails where DeductionDate between @startDate and @endDate and EmpID=@EmpID)OtherDeduction
            from Employee e where e.Is_Active='True' and e.Foreigner='False' and e.Id=@EmpID
  
     declare @startDate date, @endDate date, @EmpID int
      set @startDate='2015-09-01'
      set @endDate='2015-09-30'
      set  @EmpID='3'
      select e.Id,e.BasicSalary,e.Allowance,SUM(ts.NormalHours)W_Hrs,
      (SELECT
                 (DATEDIFF(dd, @startDate, @endDate) + 1)
                -(DATEDIFF(wk, @startDate, @endDate) * 2)
                -(CASE WHEN DATENAME(dw, @startDate) = 'Sunday' THEN 1 ELSE 0 END)
                -(CASE WHEN DATENAME(dw, @endDate) = 'Saturday' THEN 1 ELSE 0 END))W_days,
          SUM(ts.OT150Hrs)OT150Hrs,SUM(ts.OT200Hrs)OT200Hrs, SUM(ts.OT300Hrs)OT300Hrs,SUM(ts.Night130Hrs) Night130Hrs,
          SUM(ts.NightOT150Hrs)NightOT150Hrs,SUM(ts.NightOT200Hrs)NightOT200Hrs,SUM(ts.NightOT300Hrs)NightOT300Hrs,
          (SELECT COUNT(case 
                  WHEN DATENAME(DW,sch.DutyDate) not in('Saturday','Sunday') THEN
                      1
                  ELSE 0 end 
                )WeekdayDutyDays,
  
          (SELECT COUNT(case 
                  WHEN DATENAME(DW,sch.DutyDate)  in('Saturday','Sunday') THEN
                      1
                  ELSE 0 end 
                )WeekendDutyDays,
          SUM(ed.EarningAmount)OtherEarning,
          (SELECT case e.Is_Probation
                  WHEN  'False' THEN
                  e.BasicSalary*0.08
                  ELSE 0 end 
                )SI,
                (SELECT case e.Is_Probation
                  WHEN  'False' THEN
                  e.BasicSalary*0.015
                  ELSE 0 end 
                )HI,
                (SELECT case e.Is_Probation
                  WHEN  'False' THEN
                  e.BasicSalary*0.01
                  ELSE 0 end 
                )UI,
                (SELECT case e.Is_Probation
                  WHEN  'False' THEN
                  case  when e.BasicSalary &gt; 4000000 or e.BasicSalary = 4000000 then  20000   else 15000 end
                  ELSE 0 end 
                )UnionFee,
                (SELECT case e.Is_Probation
                  WHEN  'False' THEN
                      e.Dependants * 3600000
                  ELSE 0 end 
                )DependentDeduction,
               SUM(ded.DeductionAmount) OtherDeduction
  
      from Employee e 
      inner join TimeSheetA ts
      on e.Id=ts.EmpID and e.Is_Active='True' and e.Foreigner='False' 
      inner join NightDutySchedule sch
      on e.Id=sch.EmpID and sch.DutyDate &gt;= @startDate and sch.DutyDate &lt;= @endDate
      inner join  EarningDetails ed
      on e.Id=ed.EmpID and ed.EarningDate &gt;= @startDate and ed.EarningDate &lt;= @endDate
      inner join DeductionDetails ded
      on e.Id= ded.EmpID and ded.DeductionDate &gt;= @startDate and ded.DeductionDate &lt;= @endDate
  </code></pre>
  
  <p>I modifed to use inner join statement but when I excute , it display the error like this: </p>
  
  <pre><code>    Msg 102, Level 15, State 1, Line 62
  Incorrect syntax near '@endDate'.
  </code></pre>
  
  <p>Line 62 is: on e.Id= ded.EmpID and ded.DeductionDate >= @startDate and ded.DeductionDate &lt;= @endDate (it is the last one, why?)</p>