TVP存储过程工资单必须声明标量变量 [英] TVP stored procedure payroll must declare scalar variable

问题描述

我在最后一个执行语句中使用@employeeinfo执行时遇到问题。这是声明问题还是字符串问题？我该怎么办？



我试图通过一个为员工生成工资单的存储过程。



@employeeinfo应该通过TVP，但我得必须声明标量变量@employeeinfo



提前谢谢

Hi,
Im currently having an issue when executing with @employeeinfo at the last execute statement. Is this a declaration issue or string issue? how can i fix?

Im trying to pass through a stored procedure that generates a payroll for an employee.

@employeeinfo should be passing through a TVP but I'm getting "must declare scalar Variable @employeeinfo"

Thanks in advance

IF EXISTS(SELECT * FROM sys.Databases WHERE name='usp_createPayroll')
    BEGIN
        DROP PROCEDURE dbo.usp_createPayroll
        DROP TYPE dbo.EmployeeInfo
        DROP TYPE dbo.AllowanceInfo
    END
    
    CREATE TYPE EmployeeInfo AS TABLE 
    (
        employeeID INT
        ,TotalHours INT
        ,PRIMARY KEY (
            employeeID
            ,TotalHours
            )
    );
    GO
    CREATE TYPE AllowanceInfo AS TABLE 
    (
        employeeID INT, allowanceID INT, Amount DECIMAL(7, 2),PRIMARY KEY (
            employeeID, allowanceID, Amount
            )
    );
    GO
    
    CREATE PROCEDURE usp_createPayroll @StartDatePeriod DATE ,@EndDatePeriod DATE, @taxID INT, @numberofTotalHours EmployeeInfo READONLY, @Allowance AllowanceInfo READONLY
    AS
    BEGIN
    	DECLARE @stDate DATE,
    			@EdDate DATE,
    			@tx	INT,
    			@noHours INT,
    			@allow INT
    	SELECT p.employeeID,
    		  @taxID, @StartDatePeriod,
    		  @EndDatePeriod,
    		  p.TotalHours,
    		  pos.hourlyRate * p.TotalHours,
    		 (pos.hourlyRate * p.TotalHours) + h.AmountSold,
    		 ((pos.hourlyRate * p.TotalHours) + h.AmountSold) * t.taxRate, 
    		 ((pos.hourlyRate * p.TotalHours) + h.AmountSold) - (((pos.hourlyRate * p.TotalHours) + h.AmountSold) * t.taxRate)
    	FROM PaySlip p
    	INNER JOIN HumanResources h ON h.EmployeeID = p.EmployeeID
    	INNER JOIN Taxes t ON t.TaxID = p.TaxID
    	INNER JOIN Position pos ON pos.HourlyRate = p.EmployeeID
    	-- add position in somehow
    
    END
    
    
    DECLARE @employeeInfo EmployeeInfo;
    DECLARE @TotalHours INT;
    
    INSERT @employeeInfo
    SELECT e.employeeID, @TotalHours
    FROM HumanResources e
    WHERE e.employeeID = 2 AND @TotalHours = 30
    
    DECLARE @allowanceInfo AllowanceInfo;
    DECLARE @employeeAllowanceInfo EmployeeInfo;
    
    INSERT @allowanceInfo
    SELECT e.employeeID, pa.allowanceID, pa.Amount
    FROM HumanResources e, Allowance a, PayAllowance pa, EmployeeAllowance ea, @employeeAllowanceInfo emp
    WHERE e.employeeID = emp.employeeID AND pa.AllowanceID = a.AllowanceID AND a.AllowanceID = ea.AllowanceID AND pa.allowanceID = 1
    
    EXECUTE usp_createPayroll 
         @startDatePeriod = '01-01-2015 23:59:59'
        ,@endDatePeriod = '01-31-2015 23:59:59'
        ,@taxID = 2
        ,@numberofTotalHours = @employeeInfo
        ,@Allowance = @allowanceInfo
    GO
    DROP PROCEDURE usp_createPayroll

我尝试过：



我试过更改select语句并在最初我有一个插入语句时声明变量

What I have tried:

Ive tried changing the select statement and declaring the variables when originally i had an insert statement

推荐答案

除非我忘记包含，否则对我来说很好来自 DECLARE @employeeInfo EmployeeInfo; 的代码如果我在点击F5之前从我的选择中省略了DECLARE，那么我得到相同的你错了。



I.e.你必须运行以下所有：

Works fine for me unless I forget to include the code from DECLARE @employeeInfo EmployeeInfo;If I omit that DECLARE from my selection before hitting F5 then I get the same error as you.

I.e. you have to run all of the following:

DECLARE @employeeInfo EmployeeInfo;
  DECLARE @TotalHours INT;

  INSERT @employeeInfo
  SELECT e.employeeID, @TotalHours
  FROM HumanResources e
  WHERE e.employeeID = 2 AND @TotalHours = 30

  DECLARE @allowanceInfo AllowanceInfo;
  DECLARE @employeeAllowanceInfo EmployeeInfo;

  INSERT @allowanceInfo
  SELECT e.employeeID, pa.allowanceID, pa.Amount
  FROM HumanResources e, Allowance a, PayAllowance pa, EmployeeAllowance ea, @employeeAllowanceInfo emp
  WHERE e.employeeID = emp.employeeID AND pa.AllowanceID = a.AllowanceID AND a.AllowanceID = ea.AllowanceID AND pa.allowanceID = 1

  EXECUTE usp_createPayroll
       @startDatePeriod = '01-01-2015 23:59:59'
      ,@endDatePeriod = '01-31-2015 23:59:59'
      ,@taxID = 2
      ,@numberofTotalHours = @employeeInfo
      ,@Allowance = @allowanceInfo
  GO

我无法真正看到创建存储过程只是为了在运行后立即删除它它。



我不认为

I can't really see the point of creating a Stored Procedure just to drop it immediately after running it.

And I don't think

IF EXISTS(SELECT * FROM sys.Databases WHERE name='usp_createPayroll')
    BEGIN
        DROP PROCEDURE dbo.usp_createPayroll
        DROP TYPE dbo.EmployeeInfo
        DROP TYPE dbo.AllowanceInfo
    END

正在做你认为的事情 - 它应该是

is doing what you think it is - it should probably be

IF EXISTS(SELECT * FROM sys.procedures WHERE name='usp_createPayroll')
...

这篇关于TVP存储过程工资单必须声明标量变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！