Kevin Robinson
asked on
Return Zero instead of null
I have a function below which works just fine but I would like to return 0 (Zero) instead of null. I had a go here as you can see, but it does not work this way.
ALTER FUNCTION [dbo].[ufn_GetWorkedHoursW eekEnd]
(
@UserName varchar(50),
@Month varchar(50),
@Year int
)
RETURNS decimal(9,2)
BEGIN
DECLARE @WorkedHours decimal(9,2) =0
Select @WorkedHours = (SELECT Sum([TIME]) as Total FROM [Timesheets].[dbo].[TIMESH EET]
WHERE [IdVolNow] = @Username
AND Year([Date]) = @Year
AND DATENAME(month, [Date]) = @Month
AND (datename(dw,[TIMESHEET].[ Date]) = 'Saturday' OR
datename(dw,[TIMESHEET].[D ate]) = 'Sunday'))
IF @WorkedHours = Null
BEGIN
SET @WorkedHours = 0
END
Return @WorkedHours
END
ALTER FUNCTION [dbo].[ufn_GetWorkedHoursW
(
@UserName varchar(50),
@Month varchar(50),
@Year int
)
RETURNS decimal(9,2)
BEGIN
DECLARE @WorkedHours decimal(9,2) =0
Select @WorkedHours = (SELECT Sum([TIME]) as Total FROM [Timesheets].[dbo].[TIMESH
WHERE [IdVolNow] = @Username
AND Year([Date]) = @Year
AND DATENAME(month, [Date]) = @Month
AND (datename(dw,[TIMESHEET].[
datename(dw,[TIMESHEET].[D
IF @WorkedHours = Null
BEGIN
SET @WorkedHours = 0
END
Return @WorkedHours
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Return isnull(@WorkedHours, 0)
-> IF @WorkedHours IS Null