Stored Procedure. Return 0 instead of null

[cheesepie2k7]

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

[chrissie76]

why not put the mysql_num_rows into a var and then just
if ($yourvar == "NULL"){
$yourvar = 0;
}
easy

[rarepearldesign]

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);

[longneck]

don't use ISNULL. use COALESCE instead, since it's standard SQL, whereas ISNULL is mysql-specific.