asp.net - SQL SUM negative value not being returned -

i have sql query looks -

    create procedure [dbo].[getmemberfundunits]     -- add parameters stored procedure here     @member int,     @fundcode varchar(15),     @closingdate datetime begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;       -- insert statements procedure here     select (case when sum(units) null 0 else sum(units) end) fundunits     pe_minv     pmi_member = @member , pmi_fund = @fundcode , pmi_invested <= @closingdate  end 

when run stored procedure -

declare @return_value float /*int*/  exec    @return_value = [dbo].[getfundunits]         @member = 9738,         @fundcode = n'58193',         @closingdate = n'07/21/2011'  select  'return value' = @return_value 

i 2 results. first correct 1 -0.0060 , second @return_value 0

this case when call stored procedure code, 0 instead of -0.0060 want.

this how calling stored procedure code:

 dim ds new dataset()      dim cmd new sqlcommand("getmemberfundunits", conn)     cmd.commandtype = commandtype.storedprocedure      dim p_pm_member new sqlparameter("@member", sqldbtype.int)     p_pm_member.value = pm_member     cmd.parameters.add(p_pm_member)      dim p_fund_code new sqlparameter("@fundcode", sqldbtype.varchar)     p_fund_code.value = fund_code     cmd.parameters.add(p_fund_code)      dim p_period_closing_date new sqlparameter("@closingdate", sqldbtype.datetime)     p_period_closing_date.value = period_closing_date     cmd.parameters.add(p_period_closing_date)      dim da new sqldataadapter(cmd)     da.fill(ds) 

does have ideas why might happening?

thanks!

declare @return_value int 

your variable defined int. int can't hold decimal values, return value rounded. try declare var float.

list of sql server data types