如何在程序中创建表并在select with join中使用？ [英] How do I Create table in procedure and use in in select with join?

问题描述

DECLARE	@return_value table(BonusSalary int,testid int)
insert	@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
--WHERE        (Salary.Active = 1) AND (EmpAddress.Active = 1) AND (EmpEmail.Active = 1) AND (Contracts.Active = 1) AND (Emp_Contracts.Active = 1) AND (EmpPhone.Active = 1) AND (WorkTimes.Active = 1) AND 
--                         (Emp_Positions_Company.Active = 1) AND (MainCompany.Active = 1) AND (Positions.Active = 1) AND (Emp.Active = 1) AND (ContractStartEnd.Active = 1) AND (Emp_Salary.Active = 1) AND 
--                         (BonusSalary.Active = 1) OR
--                         (Salary.Active IS NULL) AND (EmpAddress.Active IS NULL) AND (EmpEmail.Active IS NULL) AND (Contracts.Active IS NULL) AND (Emp_Contracts.Active IS NULL) AND (EmpPhone.Active IS NULL) AND 
--                         (WorkTimes.Active IS NULL) AND (Emp_Positions_Company.Active IS NULL) AND (MainCompany.Active IS NULL) AND (Positions.Active IS NULL) AND (Emp.Active IS NULL) AND (ContractStartEnd.Active IS NULL)
--                          AND (Emp_Salary.Active IS NULL) AND (BonusSalary.Active IS NULL)

i got错误消息

消息137 ，Level 16，State 1，Line 14

必须声明标量变量@return_value。

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

推荐答案

创建过程语法不正确紧随其后。语法应以 CREATE PROCEDURE 开头。

尝试创建存储过程 [ ^ ]。

Create procedure syntax is not correctly followed. Syntax should start with CREATE PROCEDURE.
Try Create a Stored Procedure[^].

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

some一个帮我修复stackoverflow，所以我分享它

some one help me to fix it in stackoverflow so i share it

这篇关于如何在程序中创建表并在select with join中使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！