SQL Server Error: Subquery returned more than 1 value

Last Reply 4 months ago By maideen

Posted 4 months ago

Mr.Muddassar

I have an error in SP. The error is 

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

Pls advise me

Thank you

Maideen

SP

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

Funcation

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

 

Posted 4 months ago

Hi 

thank  you all. Solved issue by using below code

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

Thanks once again for giving me your advice.

Maideen