CODEWITHSUNDEEP
sql-servervb.net
Rich
Rich

Reputation: 271

Stored procedure output parameters return empty

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.

Upvotes: 2

Views: 1347

Answers (2)

Bugs
Bugs

Reputation: 4489

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:

enter image description here

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.

Upvotes: 0

devil_coder
devil_coder

Reputation: 1135

You need to define direction as return something like this:

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

Upvotes: 3

Related Questions