sql - 返回数据，即使它们是错误的

Question

我有一个存储过程，其中返回一个包含date，presence，total working hour(如果存在)的表...

Total Working Hour 是在一个单独的函数中计算得出的，该函数返回一个十进制值，但是有时员工在数据上有错误，例如重复打孔或两次打孔，
如果存在上述任何情况，则该函数返回错误，因此SP返回错误...

我的要求是发生任何错误时，我想返回0或-1或其他值来代替错误，以强制SP返回数据，即使它们是错误。
每次生成的错误是:

SQL Server Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >=

因此，我希望每次发生此错误时都返回一个值而不是它，

SP代码为:

ALTER PROCEDURE [dbo].[SM_GetAttendance]
(
    @StartDate date ,
    @EndDate date ,
    @EmployeeID NVARCHAR(6) 
)
AS
BEGIN

    SET NOCOUNT ON;
DECLARE dte_Cursor CURSOR FOR 
WITH T(date)
AS
( 
SELECT @StartDate 
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @EndDate
)
SELECT date FROM T OPTION (MAXRECURSION 32767);

    DECLARE @date NVARCHAR(20);
    CREATE TABLE #datetable(date DATETIME,Status NVARCHAR(50),nbOfWorkingHour DECIMAL(36,2))

--SELECT date FROM T 
set @date = ''
OPEN dte_Cursor
FETCH NEXT FROM dte_Cursor INTO @date

WHILE @@FETCH_STATUS = 0
BEGIN
insert #datetable 

SELECT
        cast((select distinct Convert(Nvarchar(12),date,102) from Attendance where date = @date
                            and employeeid =@EmployeeID ) as nvarchar(30))
                        date

,CASE WHEN  EXISTS (select 1 from Attendance 
                        where employeeid=@EmployeeID and date = @date)
                then 'Present'
                else 'absent'
                        end Status

,dbo.GetWorkingHourPerDay(@date,@EmployeeID) as numberOFWorkingHour
FETCH NEXT FROM dte_Cursor INTO @date
END 

CLOSE dte_Cursor;
DEALLOCATE dte_Cursor;
end

而得到错误的功能代码部分是:

SET @From = (SELECT Time from @Tbl where date = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPIN')

set @to = (CASE WHEN EXISTS(SELECT Times from @Tbl where dates = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT' )
then (SELECT Time from @Tbl where date = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT' )
else (SELECT Top 1 Time from @Tbl where date = dateadd(day,1,@Date) AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT')
end)

return ROUND(CAST(DATEDIFF(@From,@to) AS decimal)/ 60,2)

以下获得错误的代码:

(CASE WHEN EXISTS(SELECT Times from @Tbl where dates = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT' )

在此示例中，员工有以下两个出拳方式:

EMPID       Date            Time            Status
123         2015-10-22      06:54:42 AM     OUT 
123         2015-10-22      04:35:02 PM     OUT 

那么如何处理这种状态呢？

Answer 1

你可以改变

(CASE WHEN EXISTS(SELECT Times from @Tbl where dates = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT' )

对此

(CASE WHEN EXISTS(SELECT top 1 Times from @Tbl where dates = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT' )