none
SQL udf returns incorrect value

    Question

  • I have a UDF that returns the number of hours worked in a day from table. 

    UDF;

    [dbo].[usp_GetDailyTime]
    (

    @Consultant as nvarchar(20),
    @DateofWork as Date
    )
    RETURNS decimal
    AS
    BEGIN

    DECLARE @TotalTime decimal(18,2)

    SELECT @TotalTime = SUM(Hours_Actual)
    FROM Time_Entry 
    WHERE 
    Member_ID = @Consultant
    AND Date_start = @DateofWork

    RETURN @TotalTime

    END

    If I execute the following query

    SELECT dbo.usp_GetDailyTime('bsmith', '07/05/2013') as Total

    Returned value is 0

    (SELECT SUM(Hours_Actual) as time
    FROM Time_Entry 
    WHERE 
    Member_ID = 'bsmith'
    AND DateDiff(d, Date_start , '07/05/2013') = 0)

    Returned value is 0.30 (This value is the total of time entries for the day. so this is correct)

    The Field int he table is defined as a decimal(18.2)

    If I modify the UDF to count the records it is seeing the correct number of records. 

    SQL version 

    Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
    Feb 10 2012 19:39:15 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
    Saturday, July 06, 2013 6:59 PM

Answers

All replies

  • Change RETURNS decimal to RETURNS decimal(18, 2)
    • Marked as answer by DeanSanderson Saturday, July 06, 2013 7:13 PM
    Saturday, July 06, 2013 7:09 PM
  • Thank you, I can't believe it was that simple. 
    Saturday, July 06, 2013 7:14 PM
  • no worries, sometimes you just need an extra pair of eyes to pick the small things up ;)
    Saturday, July 06, 2013 7:19 PM
  • Why did you put this in a UDF? It will not optimize and cannot port.  Why do you avoid ISO-8601 date formats? Why did you confuse rows and records?  Fields and columns? Your mindset is locked in procedural coding and you want to make your SQL look like COBOL, BASIC or whatever language you know. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, July 07, 2013 3:13 PM
  • I agree with Celko, the above does not look like a good candidate for a scalar function, you can always include this query directly in the main query or use table valued inline function.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, July 07, 2013 9:45 PM
    Moderator