子查询返回的值超过1。当子查询遵循=,!=,<, <= , >,> =或当子查询用作
问题描述:
时,我不允许这种查询,但是,当我从C#
执行它时,出现一个错误消息,说子查询返回的值超过1。当子查询遵循=,!=,<, <= , >,> =或当子查询用作
“子查询返回大于1倍的值。这是不允许的 当子查询跟随=,!=,<,< =,>,> =,或当子查询用作 表达”。
这里是我的代码:
CREATE PROC spAddEmployee
(
@empEmail AS VARCHAR(MAX),
@empLastName AS VARCHAR(MAX),
@empFirstName AS VARCHAR(MAX),
@empMgrEmail AS VARCHAR(MAX),
@projId AS INT,
@projName AS VARCHAR(MAX),
@projStatus AS VARCHAR(MAX),
@projMgr AS VARCHAR(MAX),
@pdRole AS VARCHAR(MAX),
@pdShift AS VARCHAR(MAX),
@pdAccount AS VARCHAR(MAX),
@pdLvl AS VARCHAR(MAX),
@pdStatus AS VARCHAR(MAX),
@projMonth AS VARCHAR(MAX),
@projYear AS VARCHAR(MAX),
@projReqFte AS DECIMAL
)
AS
BEGIN
--Check if the employee already existed.
DECLARE @isEmpEmailMatch VARCHAR(MAX)
SET @isEmpEmailMatch = (SELECT COUNT(*) FROM Employees WHERE [email protected])
--Check if the project already existed.
DECLARE @isProjIdMatch VARCHAR(MAX)
SET @isProjIdMatch = (SELECT COUNT(*) FROM Projects WHERE [email protected])
IF @isEmpEmailMatch < 1
BEGIN
--Insert data to employees table.
INSERT INTO Employees (Emp_Email, Emp_LastName, Emp_FirstName, Emp_MgrName, Emp_Created_On, Emp_Updated_At)
VALUES (@empEmail, @empLastName, @empFirstName, @empMgrEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
--Insert data to Employee FTE Allocation Total
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Nov', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Dec', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Jan', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Feb', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Mar', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Apr', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('May', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Jun', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Jul', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Aug', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Sep', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Oct', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Nov', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Dec', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Jan', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Feb', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Mar', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Apr', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('May', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Jun', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Jul', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Aug', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Sep', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Employee_FTE_Allocation_Total (ET_FTE_Month, ET_FTE_Year, ET_FTE_Req, Emp_Email, ET_FTE_Created_On, ET_FTE_Updated_At)
VALUES ('Oct', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @empEmail, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
END
IF @isProjIdMatch < 1
BEGIN
--Insert data to Projects table.
INSERT INTO Projects (Proj_Id, Proj_Name, Proj_Status, Proj_MgrName, Proj_Created_On, Proj_Updated_At)
VALUES (@projId, @projName, @projStatus, @projMgr, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Jan', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Feb', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Mar', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Apr', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('May', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Jun', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Jul', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Aug', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Sep', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Oct', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Nov', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Dec', CAST(YEAR(GETDATE()) AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Jan', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Feb', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Mar', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Apr', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('May', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Jun', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Jul', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Aug', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Sep', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Oct', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Nov', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
INSERT INTO Project_FTE_Allocation_Total (PT_FTE_Month, PT_FTE_Year, PT_FTE_Req, Proj_Id)
VALUES ('Dec', CAST(YEAR(GETDATE()) + 1 AS VARCHAR(MAX)), 0, @projId)
END
-- Insert data to Project Details table.
INSERT INTO Project_Details (Proj_Id, PD_Role, PD_Level, PD_AccountName, PD_Shift, PD_Status, PD_Created_On, PD_Updated_At)
VALUES (@projId, @pdLvl, @pdRole, @pdAccount, @pdShift, @pdStatus, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
--Get the last inserted Project Detail table.
DECLARE @lastPDId INT
SET @lastPDId = (SELECT TOP 1 PD_Id FROM Project_Details ORDER BY PD_Id DESC)
--Insert data to Project FTE Allocation table.
INSERT INTO Project_FTE_Allocation (P_FTE_Month, P_FTE_Year, P_FTE_Req, PD_Id, P_FTE_Created_On, P_FTE_Updated_At)
VALUES (@projMonth, @projYear, @projReqFte, @lastPDId, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
DECLARE @projCurrentTotalFte DECIMAL
SET @projCurrentTotalFte = (SELECT PT_FTE_Req FROM Project_FTE_Allocation_Total WHERE [email protected] AND [email protected] AND
[email protected])
DECLARE @projUpdatedTotalFte DECIMAL
SET @projUpdatedTotalFte = @projCurrentTotalFte + @projReqFte
UPDATE Project_FTE_Allocation_Total SET [email protected] WHERE [email protected] AND [email protected] AND
[email protected]
--Insert data to Employee FTE Allocation Breakdown table.
INSERT INTO Employee_FTE_Allocation_Breakdown (EB_FTE_Month, EB_FTE_Year, EB_FTE_Req, EB_FTE_Created_On, EB_FTE_Updated_At)
VALUES (@projMonth, @projYear, @projReqFte, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
DECLARE @highestEBFTEId INT
SET @highestEBFTEId = (SELECT TOP 1 EB_FTE_Id FROM Employee_FTE_Allocation_Breakdown ORDER BY EB_FTE_Id DESC)
DECLARE @getRoleId INT
SET @getRoleId = (SELECT Role_Id FROM Roles WHERE [email protected])
DECLARE @getLvlId INT
SET @getLvlId = (SELECT Level_Id FROM Levels WHERE [email protected])
DECLARE @getShiftId INT
SET @getShiftId = (SELECT Shift_Id FROM Shifts WHERE [email protected])
DECLARE @isEmployeeAssignMatch VARCHAR(MAX)
SET @isEmployeeAssignMatch = (SELECT COUNT(*) FROM Assignments WHERE [email protected] AND [email protected] AND [email protected] AND [email protected]
AND [email protected] AND [email protected] AND [email protected])
IF @isEmployeeAssignMatch < 1
BEGIN
INSERT INTO Assignments (Emp_Email, Proj_Id, PD_Id, EB_FTE_Id, Role_Id, Level_Id, Shift_Id, Assign_Created_On,
Assign_Updated_At) VALUES (@empEmail, @projId, @lastPDId, @highestEBFTEId, @getRoleId, @getLvlId, @getShiftId, CURRENT_TIMESTAMP
, CURRENT_TIMESTAMP)
END
DECLARE @currentFte DECIMAL
SET @currentFte = (SELECT ET_FTE_Req FROM Employee_FTE_Allocation_Total WHERE [email protected] AND [email protected] AND
[email protected])
DECLARE @updatedFte DECIMAL
SET @updatedFte = @currentFte + @projReqFte
UPDATE Employee_FTE_Allocation_Total SET [email protected] WHERE [email protected] AND [email protected] AND
[email protected]
DECLARE @projFTEId INT
SET @projFTEId = (SELECT P_FTE_Id FROM Project_FTE_Allocation WHERE [email protected] AND [email protected] AND [email protected])
DECLARE @projCurrentFte DECIMAL
SET @projCurrentFte = (SELECT P_FTE_Req FROM Project_FTE_Allocation WHERE [email protected])
DECLARE @multipliedFte DECIMAL
SET @multipliedFte = @projReqFte * -1.0
DECLARE @projUpdatedFte DECIMAL
SET @projUpdatedFte = @projCurrentFte + @multipliedFte
UPDATE Project_FTE_Allocation SET [email protected] WHERE [email protected]
DECLARE @projTotalCurrentFte DECIMAL
SET @projTotalCurrentFte = (SELECT PT_FTE_Req FROM Project_FTE_Allocation_Total WHERE [email protected] AND [email protected] AND
[email protected])
DECLARE @projTotalUpdatedFte DECIMAL
SET @projTotalUpdatedFte = @projTotalCurrentFte + @multipliedFte
UPDATE Project_FTE_Allocation_Total SET [email protected] WHERE [email protected] AND [email protected] AND
[email protected]
END
答
尝试修改这些声明:
DECLARE @projCurrentTotalFte DECIMAL
SET @projCurrentTotalFte = (SELECT PT_FTE_Req FROM Project_FTE_Allocation_Total WHERE [email protected] AND [email protected] AND
[email protected])
和
我觉得从他们身上了错误,你期望一个结果之后=
但他们中的一个归来不止一个结果
那么,什么是问题? – Susilo
问题在于其中一个'set @foo =(select ... from ... where ...)'语句。其中一个select语句返回多个记录,这就是错误的原因。 – Harsh
所以,试着从'top 1'这样的查询中只得到一个结果,你只有两条语句,你必须这样做:SET @projCurrentTotalFte =(SELECT PT_FTE_Req FROM Project_FTE_Allocation_Total WHERE Proj_Id = @ projId AND PT_FTE_Month = @ projMonth AND PT_FTE_Year = @ projYear)'和'DECLARE @projTotalCurrentFte DECIMAL SET @projTotalCurrentFte =(SELECT PT_FTE_Req FROM Project_FTE_Allocation_Total WHERE PROJ_ID = @ PROJID AND PT_FTE_Month = @ projMonth AND PT_FTE_Year = @ projYear)' – wajeeh