Return a 0 instead of null

I need to verify if I have partial sales of certain items. I request data
from the server and I am getting NULL.
select sum(matrixamount) matrix
from sales
where invid =@.MerchID
and assettype = 2
If there are NO transactions in Sales for invid = @.MerchID I'd love to
return a 0.
I have tried :
CREATE FUNCTION dbo.GetMatrixTotal
( @.iid int )
RETURNS int AS
BEGIN
declare @.ret int
select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
end ) from sales
where invid = @.iid and assettype = 2
return @.ret
END
Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
But how do I flip it to 0 so there is a return back to a data container in
..NET
TIA
__Stephen
Try
select ISNULL(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID
and assettype = 2
Mike A.
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:OvYQa73aFHA.1148@.tk2msftngp13.phx.gbl...
>I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> .NET
> TIA
> __Stephen
>
>
|||Try,
select isnull(sum(matrixamount), 0) matrix
from sales
where invid =@.MerchID and assettype = 2
AMB
"Stephen Russell" wrote:

> I need to verify if I have partial sales of certain items. I request data
> from the server and I am getting NULL.
> select sum(matrixamount) matrix
> from sales
> where invid =@.MerchID
> and assettype = 2
> If there are NO transactions in Sales for invid = @.MerchID I'd love to
> return a 0.
> I have tried :
> CREATE FUNCTION dbo.GetMatrixTotal
> ( @.iid int )
> RETURNS int AS
> BEGIN
> declare @.ret int
> select @.ret =sum(case when matrixamount IS NULL then 0 else matrixamount
> end ) from sales
> where invid = @.iid and assettype = 2
> return @.ret
> END
> Still gives NULL, which I comprehend as being correct NO TRANSACTIONS.
> But how do I flip it to 0 so there is a return back to a data container in
> ..NET
> TIA
> __Stephen
>
>