How do I Create table in procedure and use in in select with join? [duplicate]

This question already has an answer here:

这个问题在这里已有答案:

• T-SQL: How to join @variable tables (another try) 2 answers

T-SQL:如何加入@variable表(另一个尝试)2个答案

DECLARE @return_value table(BonusSalary int, testid int)

INSERT INTO @return_value 
SELECT ( 
        SUM(case when BonusSalary.Type=1 and BonusSalary.Active=1 then BonusSalary.BonusSalary else 0 end) 
        - SUM(case when BonusSalary.Type=0 and BonusSalary.Active=1 then BonusSalary.BonusSalary else 0 end)
    ) AS Expr1, EmpID
FROM BonusSalary
GROUP BY EmpID 

SELECT  Emp.ID, Emp.EmpName, Emp.NID, EmpEmail.Email, EmpAddress.Address, EmpPhone.Phone, EmpPhone.PhoneType, Salary.Salary, 
        Salary.SalaryType, Positions.PositionName, MainCompany.Name, WorkTimes.StartTime, WorkTimes.EndTime, Contracts.Contract, 
        Contracts.Type, Emp_Contracts.ContractStartDate,@return_value.BonusSalary --, ContractStartEnd.Length, ContractStartEnd.Enddate, ContractStartEnd.Startdate
FROM Emp 
left outer join EmpPhone 
        on Emp.ID=EmpPhone.EmpID
left outer join EmpEmail 
        on Emp.ID=EmpEmail.EmpID
left outer join EmpAddress 
        on Emp.ID=EmpAddress.EmpID
left outer join Emp_Salary 
        ON Emp.ID = Emp_Salary.EmpID 
left outer join Salary 
        ON Emp_Salary.SalaryID = Salary.ID
left outer join Emp_Positions_Company 
        ON Emp.ID = Emp_Positions_Company.EMPID 
left outer join MainCompany 
        ON MainCompany.ID = Emp_Positions_Company.CompanyID 
left outer join Positions 
        on Positions.ID=Emp_Positions_Company.PositionID
left outer join WorkTimes 
        on Emp.ID=WorkTimes.EmpID
left outer join Emp_Contracts 
        on Emp.ID=Emp_Contracts.EmpID 
left outer join Contracts 
        on Contracts.ID=Emp_Contracts.ContractID 
LEFT OUTER JOIN @return_value 
        on Emp.ID = @return_value.testid

i got error message Msg 137, Level 16, State 1, Line 14 Must declare the scalar variable "@return_value".

我得到错误消息消息137,级别16,状态1,行14必须声明标量变量“@return_value”。

1 个解决方案

#1

You cannot join table variables without using an alias. Try this:

如果不使用别名,则无法连接表变量。试试这个:

 SELECT ... ,rv.BonusSalary,...
 ....
 LEFT OUTER JOIN @return_value as rv 
    on Emp.ID = rv.testid