Loading

how do i return 0 from function when query return no record

Istudent Rana

8y861

I have a function which return total hours worked. But in some cases there will be no entry at all. In that case select query return empty records. I would like to return 0 if there is no record. How do i achieve this?

ALTER FUNCTION [dbo].[ufnGetTotalHoursWorked](@id int)
RETURNS decimal(18,2)
AS

BEGIN
DECLARE @workedHours decimal(18,2);
SELECT @workedHours = (SELECT Sum(WorkedHours) FROM [WorkedHours] WHERE EmployeeId = @id GROUP BY EmployeeId having EmployeeId=@id)
RETURN @workedHours;
END

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Accepted answer
Hello Rana,

You need to write only for that is as below
RETURN ISNULL(@workedHours,0)