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.

Solution 1

I know, It's too late to give answer. As Guffa has already suggested a good solution and it will return correct result as you want.

But, still there is another solution which i would like to share with you that.

use select statement at the end of you stored procedure instead of return statement.

      Update STOCK Set S_P_ttavail = @ttavail, S_P_ttsold=@ttsold Where S_en=@S_en And IsSample=@IsSample
    END
  Select @NetRate As NetRate
END

and execute your stored procedure by using ExecuteScalar() method. How you can avoid unnecessary parameter declaration in you stored procedure.

var dNetRate = cmd.ExecuteScalar();

but, still i would like to suggest you to use output parameter if you have no problem in creating another parameter in your stored procedure otherwise you can stick with this solution.

Solution 2

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;

Comments

[varsha]

this way do i need to supply any value to @NetRate ?

[Guffa]

@varsha: No, you only have to specify where the output value should go. See: Returning Data by Using OUTPUT Parameters

[Just code]

@Guffa @nextrate has been used for sum so need to declare new variable for output

[Guffa]

@Justcode: Not at all, just use the parameter instead of creating another variable.

[varsha]

when i am using @NetRate (same name as in declaration) the it is giving me error in stored procedure

[Guffa]

@varsha: What is the error message? Have you removed the declaration of the variable with the same name?

[varsha]

no i have not removed that from declaration . error "The variable name '@NetRate' has already been declared. Variable names must be unique within a query batch or stored procedure."

[Guffa]

@varsha: As the variable exists as a parameter, you don't need to declare it as a variable also. Just use the parameter as the variable.

[varsha]

Thank your for explaining .This is rather easy for me .

[Grant Johnson]

Why isn't this answer getting more upvotes? I'm afraid it is because of the broken English. To simply return one scaler value just select what you want returned in your stored procedure and then from C# call var dNetRate = cmd.ExecuteScaler() (casting from Object to desired type) as shown. Thanks for the this answer @Shell. I didn't want to have to add a parameter to my method just to support returning a value through it. This seems much cleaner.