title: For Loop in Stored Procedure (SQL Server 2008)
body: <p>I need to make a stored procedure to insert employees monthly salary in there payroll table. can anybody give example?</p>
  
  <p>There are two tables </p>
  
  <pre><code>Employees (EmployeeID, EmployeeName, EmployeeStatus, BasicSalary)
  </code></pre>
  
  <p>and </p>
  
  <pre><code>EmployeePayroll (PayrollID, EmployeeID, VoucherNo, BasicSalary, SalaryMonth)
  </code></pre>
  
  <ol> 
  <li> Get Total Employees From Employees Table WHERE EmployeeStatus IN ('Active')............for example let's say (50 employees)
  <li> Make a Loop for all these 50 employees and Insert salary payment voucher in table (EmployeePayroll).
  <li> In EmployeePayroll table it will be inserted with auto generated voucher no for example: 
  </ol>
  
  <pre><code>SET @PayrollID = (SELECT MAX(PayrollID) AS PayrollID FROM HR.EmployeePayroll)
  SET @VoucherNo = ('SPV-K-' + CAST(DATEPART(YY,GETDATE())AS VARCHAR) + CAST(DATEPART(MM,GETDATE())AS VARCHAR) + CAST(DATEPART(DD,GETDATE())AS VARCHAR) + '-00' + @PayrollID) 
  </code></pre>
  
  <p>So records will be like following:</p>
  
  <pre><code>PayrollID.......EmployeeID.......VoucherNo......BasicSalary.........SalaryMonth
  1..................1...........SPV-K-11501-001.....250..................1
  2..................2...........SPV-K-11501-002.....300..................1
  3..................3...........SPV-K-11501-004.....400..................1
  </code></pre>