如何解决子查询在strored过程中返回的值超过1？ [英] How to solve subquery returned more than 1 value in strored procedure?

问题描述

嗨


我在存储过程中遇到错误MSSQL 2017

Hi
I have an error in Stored Procedure MSSQL 2017

"

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or 
when the subquery is used as an expression.

当我使用以下代码时




请咨询我



谢谢



Maideen



我尝试过：



存储过程

when I am using below Code

Pls advice me

Thank you

Maideen

What I have tried:

Stored Procedure

UPDATE [dbo].[AD_Staff_Attandance] SET TotHours = 
(SELECT [dbo].[GetTotalWorkingHours] (DateOut,DateIn) AS TIMEWORKED_OUT_IN 
FROM [dbo].[AD_Staff_Attandance] )

功能

Function

ALTER FUNCTION [dbo].[GetTotalWorkingHours]
(
    @DateFrom Datetime,
    @DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
 
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
 
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
				    -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
					   -CASE
                                    WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
                                    THEN 1
                                    ELSE 0
                                END+CASE
                                        WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
                                        THEN 1
                                        ELSE 0
                                    END;
SET @TotalTimeDiff =
(
    SELECT DATEDIFF(SECOND,
                   (
                       SELECT CONVERT(TIME, @DateFrom)
                   ),
                   (
                       SELECT CONVERT(TIME, @DateTo)
                   )) / 3600.0
);
 
RETURN  (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
 
END

推荐答案

您应该考虑为什么SELECT和UPDATE中没有WHERE子句;你的SELECT可能会返回多条记录。

You should think about why you don't have a WHERE clause in your SELECT and UPDATE; your SELECT is probably returning "multiple records".

Quote：

UPDATE [ dbo]。[AD_Staff_Attandance] SET TotHours =

（ SELECT [dbo]。[GetTotalWorkingHours]（DateOut，DateIn）AS TIMEWORKED_OUT_IN

FROM [ dbo]。[AD_Staff_Attandance]）

UPDATE [dbo].[AD_Staff_Attandance] SET TotHours =
(SELECT [dbo].[GetTotalWorkingHours] (DateOut,DateIn) AS TIMEWORKED_OUT_IN
FROM [dbo].[AD_Staff_Attandance] )

您可以尝试：

UPDATE dbo.AD_Staff_Attandance
SET TotHours = dbo.GetTotalWorkingHours(DateOut, DateIn)

因为没有 WHERE 子句，此请求将更新表中的所有行;请求将持续比例为行数。

As there is no WHERE clause, this request will update all rows in the table; the request will last proportionnaly to the number of rows.

您可以执行以下操作

You can do the following

SELECT TOP 1 * FROM MyTable

现在，SELECT TOP 1 ...可以工作但是这可能不正确。 Gerry提到您的SELECT和UPDATE语句中没有WHERE子句，因此您必须查看它以确保获得正确的结果。

Now, the "SELECT TOP 1..." can work but it might not be correct. Gerry mentioned that you don't have a WHERE clause in your SELECT and UPDATE statements, so you will have to look at that in order to make sure you get the correct results.

这篇关于如何解决子查询在strored过程中返回的值超过1？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！