Stored procedure output parameters return empty

Question

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

@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.

You need to specify the direction of the parameter when you add them. The default is input. — Sean Lange
– Sean Lange, Commented May 12, 2017 at 15:31

devil_coder · Accepted Answer · 2017-05-12 15:31:41Z

3

You need to define direction as return something like this:

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

answered May 12, 2017 at 15:31

devil_coder

1,1351 gold badge10 silver badges12 bronze badges

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

Comments

Bugs · Accepted Answer · 2017-05-12 15:57:41Z

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.

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:

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.

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:

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.

Collectives™ on Stack Overflow

Stored procedure output parameters return empty

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related