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

enter image description here

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

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.

RETURN from a sproc is used to return an integer error code. If you want to output a value, you should use an OUTPUT parameter (or more typically, just SELECT it and have the caller read the value). — TZHX
– TZHX, Commented Feb 26, 2015 at 10:25
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Feb 26, 2015 at 10:26
change your SP in function, so you can return a scalar value (as your value). Alternatively, use an output parameter — Joe Taras
– Joe Taras, Commented Feb 26, 2015 at 10:26
@MilenPavlov but i need to do it manually everytime before executing .@returnValue is not declare by me in sp — varsha
– varsha, Commented Feb 26, 2015 at 10:27

Guffa · Accepted Answer · 2015-02-26 10:47:30Z

6

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;

edited Feb 26, 2015 at 10:47

answered Feb 26, 2015 at 10:28

Guffa

703k111 gold badges760 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

varsha Over a year ago

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

Guffa Over a year ago

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

Just code Over a year ago

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

Guffa Over a year ago

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

varsha Over a year ago

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

|

Shell · Accepted Answer · 2015-06-29 19:31:14Z

4

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.

answered Jun 29, 2015 at 19:31

Shell

6,86911 gold badges43 silver badges75 bronze badges

2 Comments

varsha Over a year ago

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

Grant Johnson Over a year ago

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.

Muflix · Accepted Answer · 2020-03-03 20:31:22Z

2

I had to also specify precision and scale in SqlParameter class

var output = new SqlParameter("output", SqlDbType.Decimal)
{
     Direction = ParameterDirection.Output,
     Precision = 18,
     Scale = 2
};

var sql = "exec dbo.SomeOperation @output OUT";

var result = this.Database.ExecuteSqlCommand(sql, output);

return (decimal)output.Value;

without that, I always got value without decimal points, even though the data type was decimal.

answered Mar 3, 2020 at 20:31

Muflix

6,89620 gold badges97 silver badges176 bronze badges

Collectives™ on Stack Overflow

how to return decimal value from stored procedure

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related