存储过程输出参数返回空

In my SQL Server 2014 I have a Stored procedure that returns 2 values in 2 variables as output:

在我的SQL Server 2014中，我有一个存储过程，它返回两个变量中的两个值作为输出:

@TotalNoRatio
@TotalRatio

Here are the results after execution:

以下是执行后的结果:

@TotalNoRatio   @TotalRatio
34510793        31857292

Return Value 0

Now I want those 2 values to be display in a Label on my form.

现在我希望这两个值显示在表单的标签中。

Here is the code:

这是代码:

cmd2.CommandType = CommandType.StoredProcedure
cmd2.Parameters.Add("@TotalNoRatio", SqlDbType.Decimal)
cmd2.Parameters.Add("@TotalRatio", SqlDbType.Decimal)
cmd2.ExecuteNonQuery()
Me.LTotal1.Text = cmd2.Parameters("@TotalNoRatio").Value
Me.LTotal2.Text = cmd2.Parameters("@TotalRatio").Value

Everything runs fine without errors except that the results are empty.

除了结果为空之外，一切运行正常，没有错误。

2 个解决方案

#1

You need to define direction as return something like this:

你需要将方向定义为返回如下内容:

SqlParameter retval = sqlcomm.Parameters.Add("@TotalNoRatio", SqlDbType.Decimal);
retval.Direction = ParameterDirection.ReturnValue;

#2

You will need to specify the direction of you parameters as ParameterDirection.Output. You will also need to declare your parameters on your procedure as OUTPUT.

您需要将参数的方向指定为ParameterDirection.Output。您还需要将过程中的参数声明为输出。

I have put together a small example below.

我在下面列出了一个小例子。

This is my procedure:

这是我的程序:

CREATE PROCEDURE [dbo].[procedureName]  

@TotalNoRatio DECIMAL(18,2) OUTPUT,
@TotalRatio DECIMAL(18,2) OUTPUT

AS

SET @TotalNoRatio = 2
SET @TotalRatio = 3

This is my VB.NET code:

这是我的VB。NET代码:

Using con As New SqlConnection(conString),
      cmd As New SqlCommand("procedureName", con) With {.CommandType = CommandType.StoredProcedure}

    con.Open()

    cmd.Parameters.Add(New SqlParameter("@TotalNoRatio", SqlDbType.Decimal) With {.Direction = ParameterDirection.Output})
    cmd.Parameters.Add(New SqlParameter("@TotalRatio", SqlDbType.Decimal) With {.Direction = ParameterDirection.Output})

    cmd.ExecuteNonQuery()

    lTotal1.Text = "TotalNoRatio: " & cmd.Parameters("@TotalNoRatio").Value.ToString()
    lTotal2.Text = "TotalRatio: " & cmd.Parameters("@TotalRatio").Value.ToString()
End Using

This is a screenshot of the output:

这是输出的截图:

On a seperate note consider turning Option Strict On:

在分开的注意，考虑转向选项严格打开:

Restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type.

将隐式数据类型转换限制为只扩展转换、不允许延迟绑定和不允许生成对象类型的隐式类型。

cmd.Parameters("@TotalNoRatio").Value returns type Object. You should be appending .ToString() to it if you're assigning to Label.Text.

cmd.Parameters(“@TotalNoRatio”)。值返回类型对象。如果要给Label.Text赋值，应该附加. tostring()到它。

Also note that I have implemented Using. You may already have, it's difficult to tell but if you haven't it's worth doing:

还要注意，我已经实现了Using。你可能已经有了，这很难说，但如果你还没有，那就值得去做:

Sometimes your code requires an unmanaged resource, such as a file handle, a COM wrapper, or a SQL connection. A Using block guarantees the disposal of one or more such resources when your code is finished with them. This makes them available for other code to use.

有时代码需要一个非托管资源，例如文件句柄、COM包装器或SQL连接。使用块保证在您的代码使用完一个或多个这样的资源后进行处理。这使得其他代码可以使用它们。

#1