SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Stored Procedure. Return 0 instead of null

    Hi there,

    I have this query:

    DECLARE @Day90AO MONEY
    SELECT @Day90AO=SUM(AmountOutstanding )
    FROM Invoice
    WHERE (CustomerCode = @CustomerCode) AND (DueDate < { fn NOW() } - 91) AND (AmountOutstanding > 0)
    GROUP BY CustomerCode

    Which will return a number if it finds any matching rows. But if it doesnt, then it returns null. As I am using this stored procedure for a Crystal Report, is there any way I could get it to return 0 instead of null?

    Sorry for my noob'ness!

    Cheers,
    Will

  2. #2
    SitePoint Zealot chrissie76's Avatar
    Join Date
    Sep 2007
    Location
    plymouth, devon, uk
    Posts
    113
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not put the mysql_num_rows into a var and then just
    if ($yourvar == "NULL"){
    $yourvar = 0;
    }
    easy
    Live Long Laugh Often

  3. #3
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use ISNULL as it will replace a NULL value with a replacement.

    DECLARE @Day90AO MONEY

    SELECT @Day90AO=SUM(AmountOutstanding )
    FROM Invoice
    WHERE (CustomerCode = @CustomerCode) AND (DueDate < { fn NOW() } - 91) AND (AmountOutstanding > 0)
    GROUP BY CustomerCode

    SELECT ISNULL(@Day90AO, 0);

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    don't use ISNULL. use COALESCE instead, since it's standard SQL, whereas ISNULL is mysql-specific.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


Bookmarks

Posting Permissions

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