Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    27

    Unanswered: Force Zero For Scalar SP

    I know I've done this in the past, which makes being unable to figure it out even more frustrating.

    I've got a stored procedure that's meant to return the average of a value between two given dates.
    Code:
    CREATE PROCEDURE [dbo].[stat_MyAverageValue]
    
    @Date1 DateTime,
    @Date2 DateTime
    
    AS
    
    SELECT AVG(MyValue) AS AvgValue
    FROM MyTable
    WHERE MyDate BETWEEN @Date1 AND @Date2
    The problem is that I can't guarantee that there will be any values between the given dates, meaning this could potentially return nothing at all. How do I force it to return zero instead in that case?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coalesce()
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Posts
    27
    That only works if a NULL is returned. In this case the query would return no rows, meaning not even a NULL. Is there perhaps some way to force a NULL value if no rows are returned so that Coalesce() will work?

    EDIT: Oops...in my original query there was an unnecessary GROUP BY clause which is why there was no NULL returned. Coalesce() works without that now. Thanks
    Last edited by VentureFree; 07-27-10 at 10:06. Reason: Erroneous reply. Correcting mistake.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not true, you have made an assumption. Some (not all) aggregate functions will return at least one row even when there are no rows from the source table(s) that meet the WHERE predicate.
    Code:
    SELECT  AVG(number)
    FROM    master.dbo.spt_values
    WHERE   1 = 2
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •