Click here to Skip to main content
15,889,863 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
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.



when I am using below Code

Pls advice me

Thank you

Maideen

What I have tried:

Stored Procedure

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


Function
SQL
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
Updated 26-May-19 23:09pm

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] )
 
Share this answer
 
Comments
Maideen Abdul Kader 27-May-19 4:16am    
Hi Thanks of prompt reply.
I need to update column ToHours for all rows or records.
Even in Where clause, there is same error.
Pls advice me
Thank you again
Maideen
You could try:
SQL
UPDATE dbo.AD_Staff_Attandance
SET TotHours = dbo.GetTotalWorkingHours(DateOut, DateIn)
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.
 
Share this answer
 
You can do the following

SQL
SELECT TOP 1 * FROM MyTable


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.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900