?
Solved

Return Zero instead of null

Posted on 2013-06-07
3
Medium Priority
?
467 Views
Last Modified: 2013-06-07
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_GetWorkedHoursWeekEnd]
(
@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].[TIMESHEET]
                                    WHERE [IdVolNow] = @Username
                                    AND Year([Date]) = @Year
                                    AND DATENAME(month, [Date]) = @Month
                                    AND (datename(dw,[TIMESHEET].[Date]) = 'Saturday' OR
                                    datename(dw,[TIMESHEET].[Date]) = 'Sunday'))

IF @WorkedHours = Null
BEGIN
      SET @WorkedHours = 0
END

Return @WorkedHours

END
0
Comment
Question by:Kevin Robinson
3 Comments
 
LVL 7

Accepted Solution

by:
Ross Turner earned 2000 total points
ID: 39228370
Try:

isnull(@WorkedHours,0)

http://msdn.microsoft.com/en-us/library/ms184325.aspx


ALTER FUNCTION [dbo].[ufn_GetWorkedHoursWeekEnd]
(
@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].[TIMESHEET]
                                    WHERE [IdVolNow] = @Username
                                    AND Year([Date]) = @Year
                                    AND DATENAME(month, [Date]) = @Month
                                    AND (datename(dw,[TIMESHEET].[Date]) = 'Saturday' OR
                                    datename(dw,[TIMESHEET].[Date]) = 'Sunday'))



Return isnull(@WorkedHours,0)

END
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 39228378
IF @WorkedHours = Null

-> IF @WorkedHours IS Null
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39228379
Return isnull(@WorkedHours, 0)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question