NULL

• Curtis-211043

  Right there with Babe

  Points: 769

  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?

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 14, 2005 at 12:07 pm

  #597437

  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

  October 14, 2005 at 12:26 pm

  #597440

  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

  October 14, 2005 at 12:40 pm

  #597442

  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