NULL

Question

NULL

Curtis-211043

SSC Eights!

Points: 819
More actions
October 14, 2005 at 12:00 pm

#102866

I am trying to return -1 if no records match but because of my calculation the rowcount is thrown off.
declare
@rowcount int
Select sum(isnull(saleAmount, 0)) - sum(isnull(PaymentAmount, 0)) as balance
From dbo.Transactions
where UserID= @ClientID
select @rowcount = @@rowcount
if @rowcount >0
return 0
else return -1
if no records match then it returns
Balance
--------
<Null>
How do I do it so it will return -1 if no records match?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

You are mixing two concepts:

Balance is the recordset returned by your query. Return is an output parameter you need to decide if you want your results back as recordset or as a paramenter!

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

since we were talking about perspectives a few minutes ago...how about if you just check to see if a match exists...

if exists(select * from dbo.Transactions where UserID= @ClientID)
   begin
      Select sum(isnull(saleAmount, 0)) - sum(isnull(PaymentAmount, 0)) as balance
      From dbo.Transactions
      where UserID= @ClientID
   end

..then on the clientside you know there are no rows for that ID if you get no results back ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 3

Ah ... perspectives ...

In that case let me write mine

Select @Balance = sum(isnull(saleAmount, 0)) - sum(isnull(PaymentAmount, 0))

From dbo.Transactions

where UserID= @ClientID

if @@rowcount > 0

begin

select @Balance as Balance

return 0

end

else

return -1

* Noel