title: return data even if they are errors
body: <p>I have a stored procedure, in which return a table containing <code>date</code>,<code>presence</code>,<code>total working hour</code> if present... </p>
  
  <p><strong><code>Total Working Hour</code></strong> is calculated in a separate function that return a decimal value, <strong>But</strong> sometimes employees have a mistakes in data, like a duplicate punches or punch (out or in) for twice,<br>
  if any of the above scenario exist the function returned an error, and therefore The SP return error...    </p>
  
  <p>My request is when any error is occurred, i want to return 0 or -1 or whatever instead of the error, to forces the SP to return data even if they are errors..<br>
  The Error generating every time is :    </p>
  
  <pre><code>SQL Server Subquery returned more than 1 value.
  This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;=
  </code></pre>
  
  <p>So I want in every time when this error occurred, to return a value instead of it,     </p>
  
  <p>the SP Code is:    </p>
  
  <pre><code>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 &lt; @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
  </code></pre>
  
  <p>And The Part of the Function Code that get the error is:     </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>the following piece of code that get the error : </p>
  
  <pre><code>(CASE WHEN EXISTS(SELECT Times from @Tbl where dates = @Date AND (EmployeeID=@employeeID OR ISNULL( @employeeID, '') = '') and funckey = 'EMPOUT' )
  </code></pre>
  
  <p>IN this example the employee have two out punches like the below :   </p>
  
  <pre><code>EMPID       Date            Time            Status
  123         2015-10-22      06:54:42 AM     OUT 
  123         2015-10-22      04:35:02 PM     OUT 
  </code></pre>
  
  <p>So how to how to handle this state?</p>