如何从存储过程中返回十进制值

Question

我有一个存储过程，它没有返回正确的值，例如应该返回33.30，但它返回33.00，这不是期望的结果。这是我的存储过程，我正在使用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

当我在SSMS中执行它时，我得到了结果和消息，显示了我打印的值，这表明数据获取和计算都给出了正确的结果[请参见最后一个打印的值]。 我需要将这个值保存在另一个表中，所以这是C#代码。

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;

请帮助我，谢谢。

- varsha

---

例如，将 @return_value 设为小数类型（decimal）并保留两位小数？ - Milen

存储过程中的 RETURN 用于返回一个 整数型错误代码。如果您想要输出一个值，应该使用一个 OUTPUT 参数（或者更常见的做法是通过 SELECT 语句来选择并让调用者读取该值）。 - TZHX

附注：您不应该为存储过程使用 sp_ 前缀。Microsoft已经保留了该前缀供自己使用（请参阅命名存储过程），而且您将来可能会遇到名称冲突的风险。这也会影响您的存储过程性能。最好只是避免使用 sp_，使用其他前缀或根本不使用前缀！ - marc_s

@MilenPavlov 但是我需要在每次执行之前手动执行它。@returnValue 在存储过程中不是由我声明的。 - varsha

@TZHX 你能给我一个例子让我知道如何使用这个语法吗？我已经尝试使用output，但总是出错。 - varsha

Answer 1

返回值只能是整数。

使用输出参数从过程中获取结果：

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

使用以下方式调用：

declare @Result decimal(9,2)

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

在C#中，您需要创建一个输出参数，并在调用之前将其添加到参数中：

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

在调用方法后（如果有结果集，需要读取结果集），您可以从参数中获取值：

Decimal netRate = (Decimal)result.Value;

- Guffa

---

@varsha：不，你只需要指定输出值应该放在哪里。请参阅：使用OUTPUT参数返回数据。 - Guffa

@Guffa @nextrate已经被用于求和，所以需要声明一个新变量output。 - Just code

@varsha：错误信息是什么？你是否删除了具有相同名称的变量的声明？ - Guffa

没有从声明中删除它。错误：“变量名'@NetRate'已经被声明。变量名必须在查询批处理或存储过程中是唯一的。” - varsha

@varsha：由于变量作为参数存在，您不需要将其声明为变量。只需使用参数作为变量即可。 - Guffa

Answer 2

我知道，现在回答有点晚了。Guffa已经提出了一个好的解决方案，它将返回你想要的正确结果。

但是，还有另一种解决方案我想与你分享。

在存储过程的末尾使用select语句而不是return语句。

      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

使用ExecuteScalar()方法执行存储过程。如何避免在存储过程中声明不必要的参数。

var dNetRate = cmd.ExecuteScalar();

但是，我仍然建议您在存储过程中创建另一个参数时使用输出参数，否则您可以继续使用此解决方案。

- Shell

---

谢谢您的解释。这对我来说非常容易。 - varsha

为什么这个答案没有得到更多的赞？我担心是因为英语有些不太好。要返回一个标量值，只需在存储过程中选择要返回的内容，然后从 C# 中调用 var dNetRate = cmd.ExecuteScaler()（将 Object 强制转换为所需类型），如示例所示。感谢 @Shell 的回答。我不想添加一个参数来支持通过它返回一个值。这看起来更加简洁。 - Grant Johnson

Answer 3

我还需要在 SqlParameter 类中指定精度和比例。

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;

如果没有这个设置，即使数据类型是十进制，我始终得到没有小数点的值。

- Muflix