how to return decimal value from stored procedure

Question

I have a stored procedure which is not returning correct value, for eg. it should return 33.30 but it returns 33.00 which is not desired result. this is my stored procedure ,i'm using SQL server 2008

ALTER PROCEDURE [dbo].[sp_UpdateStockForSale]
@prodName varchar(40), 
@stqty numeric(9,2),
@batchno varchar(40),
@IsSample varchar(5)
AS
BEGIN
SET NOCOUNT ON; 
DECLARE @S_en int;
DECLARE @ttavail numeric(9,0);
DECLARE @ttsold numeric(9,0);
DECLARE @Nr decimal(9,2);
DECLARE @NetRate decimal(9,2)
SET @NetRate=0.0;
While (@stqty > 0) BEGIN

    Select @S_en=S_en,@ttavail=S_P_ttavail, @ttsold=S_P_ttsold,@Nr=NetRate From STOCK WHERE S_P_ttavail>0 AND S_P_name = @prodName  AND S_P_batchno=@batchno And IsSample=@IsSample Order By S_en DESC;

    --If Sale Qty is more than Stock
    IF (@ttavail <= @stqty) BEGIN
        SET @stqty = @stqty - @ttavail;
        SET @ttsold=@ttsold + @ttavail;
        SET @NetRate=@NetRate+(@Nr*@ttavail);
        Print (@NetRate);
        SET @ttavail = 0;
    END
    --If Sale Qty is less than STOCK
    ELSE IF(@stqty < @ttavail) BEGIN
        SET @ttsold = @ttsold + @stqty      
        SET @ttavail = @ttavail - @stqty;   
        SET @NetRate=@NetRate+(@Nr*@stqty);
        Print (@NetRate);
        SET @stqty = 0;
    END 
    Update STOCK Set S_P_ttavail = @ttavail, S_P_ttsold=@ttsold Where S_en=@S_en And IsSample=@IsSample
END
print @NetRate
return @NetRate  
END

when I execute it in SSMS I get result

and message which is showing values i printed ,which shows data fetching n calculations are giving right result [see last printed value] .

I need this value to save in another table ,so this is C# code

cmd = new SqlCommand("sp_UpdateStockForSale ", conn);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@prodName", dataGridView1.Rows[i].Cells["P_name"].Value);
                                cmd.Parameters.AddWithValue("@stqty", dataGridView1.Rows[i].Cells["P_otabs"].Value); //total tabs
                                cmd.Parameters.AddWithValue("@batchno ", dataGridView1.Rows[i].Cells["P_batch_no"].Value);
                                cmd.Parameters.AddWithValue("@IsSample ", dataGridView1.Rows[i].Cells["IsSample"].Value);

                                var returnParameter = cmd.Parameters.Add("@NetRate", SqlDbType.Decimal);
                                returnParameter.Direction = ParameterDirection.ReturnValue;
                                cmd.ExecuteNonQuery();
                                var result = returnParameter.Value;

please help me .thanks.

Answer 1

The return value can only be an integer.

Use an output parameter to get the result from the procedure:

ALTER PROCEDURE [dbo].[sp_UpdateStockForSale]
  @prodName varchar(40), 
  @stqty numeric(9,2),
  @batchno varchar(40),
  @IsSample varchar(5),
  @NetRate decimal(9,2) output
AS
...

Call it using:

declare @Result decimal(9,2)

exec
  [dbo].[sp_UpdateStockForSale]
  @prodName = N'vicks',
  @stqty = 30,
  @batchno = N'v7',
  @IsSample = N'false',
  @NetRate = @Result output

In C# you would create an output parameter and add to the parameters before the call:

SqlParameter result = new SqlParameter("@NetRate", SqlDbType.Decimal);
result.Direction = ParameterDirection.Output;
command.Parameters.Add(result);

After the call (and reading the result set if there is one) you can get the value from the parameter:

Decimal netRate = (Decimal)result.Value;