Check if a field returning from stored procedure has value?

Question

I have a stored procedure like this and when I run this stored procedure in SQL Server Management Studio, I only get “Command(s) completed successfully.”

How in my vb.net code with a simple if statement I can check if @onum is 0 do something.

I never work with this kind, usually stored procedures return an empty row when it doesn't have a value.

 ALTER PROCEDURE [dbo].[o_By_RNum]
     @rNum varchar(50)
     ,@ServiceType varchar(50)
     ,@Direction varchar(1) = NULL
     ,@VendorID int
AS
   Declare @onum int

   If @Direction is Null
   Begin
      SELECT @onum = o_num
      FROM RemoteOrder
      WHERE r_num LIKE @rNum
        AND r_ServiceType = @ServiceType
        AND VendorID = @VendorID
   End
   Else
   Begin
      SELECT @onum = o_num
      FROM RemoteOrder
      WHERE r_num LIKE @rNum
        AND r_ServiceType = @ServiceType
        AND Direction = @Direction
        AND VendorID = @VendorID
   End

   RETURN ISNULL(@onum,0)

and I am calling this stored procedure in my vb.net code like this:

Private Sub DLL_GetOnumFromRemoteOrder_By_RNum(ByRef SqlConn As SqlConnection, ByRef R_NUM As String, ByRef R_SERVICETYPE As String, ByRef DIRECTION As String, ByRef VENDORID As Integer, ByRef RetVal As Integer)

    'Open the SQL Connection
    If SqlConn.State <> ConnectionState.Open Then SqlConn.Open()

    'Add Parameters
    Dim command As New SqlCommand("DLL_GetOnumFromRemoteOrder_By_RNum", SqlConn)
    command.CommandType = CommandType.StoredProcedure
    command.Parameters.Add("@rNum", SqlDbType.VarChar).Value = IIf(IsNothing(R_NUM), DBNull.Value, R_NUM)
    command.Parameters.Add("@ServiceType", SqlDbType.VarChar).Value = IIf(IsNothing(R_SERVICETYPE), DBNull.Value, R_SERVICETYPE)
    command.Parameters.Add("@Direction", SqlDbType.Char).Value = IIf(IsNothing(DIRECTION), DBNull.Value, DIRECTION)
    command.Parameters.Add("@VendorID", SqlDbType.Int).Value = IIf(IsNothing(VENDORID), DBNull.Value, VENDORID)

    command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int)
    command.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue

    'Dim dr As SqlDataReader
    'dr = command.ExecuteReader()
    'dr.Read()
    'Return dr
    'Execute the command
    RetVal = command.ExecuteNonQuery()
    RetVal = CInt(command.Parameters.Item("@RETURN_VALUE").Value)
End Sub

You execute with `ExecuteNonQuery`, so you don't get any rows. Execute with `ExecuteReader` to get rows. — GSerg, Aug 16 '13 at 23:19
I am not getting the row when I run the store proc in management studio. and they asked me put an if statement to check if onum is null or 0 do something how I get onum when store proc is not returning anything? — Alma, Aug 16 '13 at 23:23
It is returning `isnull(@onum,0)` as [its return value](http://stackoverflow.com/a/8619015/11683). — GSerg, Aug 16 '13 at 23:26

score 0 · Answer 1 · answered Aug 17 '13 at 00:14

Change your stored procedure to:

 ALTER  PROCEDURE [dbo].[o_By_RNum]

 @rNum varchar(50)
,@ServiceType varchar(50)
,@Direction varchar(1) = NULL
,@VendorID int
 AS
 BEGIN

    SELECT ISNULL(o_num,0)
    FROM RemoteOrder
    WHERE r_num LIKE @rNum
    AND r_ServiceType = @ServiceType
    AND (@Direction is Null OR Direction = @Direction)
    AND VendorID = @VendorID
  END

this way you will have actual SELECT that will show results in Management Studio.

Then instead of last 4 lines after

command.Parameters.Add("@VendorID", SqlDbType.Int).Value = IIf(IsNothing(VENDORID), DBNull.Value, VENDORID)

that add RETURN_VALUE parameter and ExecuteNonQuery just do

RetVal = command.ExecuteScalar()

score 0 · Accepted Answer · edited May 23 '17 at 12:20

Problem seems to be in the declaration of the return parameter, as there is no need to give it a name or a type, since a return value is always an int. This question also has the same problem. The code they suggest would be something like this:

Dim RetvalParameter As SqlParameter = command.Parameters.Add()
RetvalParameter.Direction = ParameterDirection.ReturnValue

command.ExecuteNonQuery()
RetVal = CInt(RetvalParameter.Value)

Check if a field returning from stored procedure has value?

2 Answers2