payroll for employee in stored procedures wth TVP

Question

I'm trying to generate employee payslips and store it in the db with this stored procedure but i encountered some problems.

It is pointing error to my execute:

Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

The parameters im inputing in are:

1. start date
2. end date
3. employee hours worked information – table-valued parameter with employee id and hours worked for the pay period.
4. employee allowance information – table-valued parameter with employee id, allowance type id and allowance amount.

Code:

CREATE TYPE EmployeeInfo AS TABLE 
(
    employeeID INT
    ,hoursWorked INT
    ,PRIMARY KEY (
        employeeID
        ,hoursWorked
        )
);
GO

CREATE TYPE AllowanceInfo AS TABLE 
(
    employeeID INT
    ,allowanceID INT
    ,allowanceAmount DECIMAL(7, 2)
    ,PRIMARY KEY (
        employeeID
        ,allowanceID
        ,allowanceAmount
        )
);
GO

CREATE PROCEDURE usp_createPayroll @startDate DATE
    ,@endDate DATE
    ,@taxID INT
    ,@noHoursWorked EmployeeInfo READONLY
    ,@allowanceBonus AllowanceInfo READONLY
AS
BEGIN
    INSERT INTO Payslip
    SELECT @startDate
        ,@endDate
        ,n.hoursWorked
        ,p.hourlyRate
        ,p.hourlyRate * n.hoursWorked
        ,(p.hourlyRate * n.hoursWorked) + a.allowanceAmount
        ,((p.hourlyRate * n.hoursWorked) + a.allowanceAmount) * t.taxRate / 100
        ,@taxID
        ,a.allowanceID
        ,n.employeeID
    FROM @noHoursWorked n
        ,@allowanceBonus a
        ,Position p
        ,Employee e
        ,Tax t
    WHERE p.positionID = e.positionID
        AND e.employeeID = n.employeeID
        AND t.taxID = @taxID
END

DECLARE @employeeInfo EmployeeInfo;
DECLARE @hoursWorked INT;

INSERT @employeeInfo
SELECT e.employeeID
    ,@hoursWorked
FROM Employee e
WHERE e.employeeID = 1
    AND @hoursWorked = 160

DECLARE @allowanceInfo AllowanceInfo;
DECLARE @empInfo EmployeeInfo

INSERT @allowanceInfo
SELECT e.employeeID
    ,a.allowanceID
    ,a.allowanceAmount
FROM Employee e
    ,Allowance a
    ,@empInfo emp
WHERE e.employeeID = emp.employeeID
    AND a.allowanceID = 1

EXECUTE usp_createPayroll @startDate = '1/1/2015'
    ,@endDate = '1/31/2015'
    ,@taxID = 2
    ,@employeeInfo
    ,@allowanceBonus

Answer 1

As the error says, when you used named parameters you need to do it for all parameters, it looks like it should be:

EXECUTE usp_createPayroll 
     @startDate = '1/1/2015'
    ,@endDate = '1/31/2015'
    ,@taxID = 2
    ,@noHoursWorked = @employeeInfo
    ,@allowanceBonus = @allowanceInfo

Also, it looks like you're declaring a variable DECLARE @empInfo EmployeeInfo that never is filled with data, but used in a join just after it's been declared. Maybe the insert query that references @empinfo is supposed to reference @employeeInfo ?