CODEWITHSUNDEEP
sql-servert-sql
Malik
Malik

Reputation: 207

For Loop in Stored Procedure (SQL Server 2008)

I need to make a stored procedure to insert employees monthly salary in there payroll table. can anybody give example?

There are two tables 

Employees (EmployeeID, EmployeeName, EmployeeStatus, BasicSalary)

and 

EmployeePayroll (PayrollID, EmployeeID, VoucherNo, BasicSalary, SalaryMonth)
  1. Get Total Employees From Employees Table WHERE EmployeeStatus IN ('Active')............for example let's say (50 employees)
  2. Make a Loop for all these 50 employees and Insert salary payment voucher in table (EmployeePayroll).
  3. In EmployeePayroll table it will be inserted with auto generated voucher no for example: 
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)

So records will be like following:

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

Upvotes: 1

Views: 12741

Answers (1)

grimmig
grimmig

Reputation: 1421

You don't need a loop for insert operations that get their data from other tables. As gbn suggested make the voucherID an IDENTITY column so it autoincrements on each insert. Then use an insert statement like this:

INSERT INTO EmployeePayroll (EmployeeID, VoucherNo, BasicSalary, SalaryMonth)
SELECT 
    EmployeeID, 
    ( 'SPV-K-' + 'rest of your voucher calculation'),
    BasicSalary, 1
FROM Employees WHERE EmployeeStatus IN ('Active')

Note that integrating the voucher-ID into the voucher-number is not possible this way and also desirable, IMHO. A better way would be to save the current date as a column "payout-day" and then generate the voucher number when selecting from this table. (Or better create a view for reading from this table and put the "VoucherNo" calculation into that views SELECT statement)

Upvotes: 1

Related Questions