ExecuteScalar returns nothing from stored procedure

Question

I have a simple stored procedure that returns the type_id (int) as an OUTPUT parameter using the type_name (varchar(100)) as the input parameter. When I execute the stored procedure in SqlServer, it works fine and returns the appropriate type_id.

CREATE PROCEDURE [dbo].[intake_types_select_by_type_name]
    @type_name varchar(100),
    @type_id integer OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON

    SELECT  @type_id = type_id
    FROM    intake_types
    WHERE   type_name = @type_name
END

However, when I call the stored procedure from a function in VisualStudio the parameter @type_id returns Nothing.

Public Shared Function sp_intake_types_select_by_type_name(ByVal conn As DBConnection, ByVal caseName As String) As Integer
    Dim sp As SqlCommand = conn.GetStoredProcedure("intake_types_select_by_type_name")
    With sp.Parameters
        .Add("@type_name", SqlDbType.VarChar, ParameterDirection.Input).Value = caseName
        .Add("@type_id", SqlDbType.Int)
        .Item("@type_id").Direction = ParameterDirection.Output
    End With
    sp.ExecuteScalar()
    If Not IsDBNull(sp.Parameters("@type_id").Value) Then
        Return sp.Parameters("@type_id").Value
    Else
        Return Nothing
    End If
End Function

I have spent hours searching the web for a solution and found nothing that helps. I have a similar stored procedure and function in a different project that uses the same coding and logic and it works fine (however it returns a varchar as the OUTPUT parameter). I have compared these two projects to see if I am missing something simple but no joy.

Answer 1

You really should use Option Strict On and correct the problems it points out. At the moment, implicit conversions between data types are being performed which will make your code much slower and possibly error-prone.

I suggest the following:

CREATE PROCEDURE [dbo].[intake_types_select_by_type_name]
    @type_name varchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    SELECT  type_id
    FROM    intake_types
    WHERE   type_name = @type_name
END

And the function to call it:

Option Strict On
' ....'

Public Shared Function sp_intake_types_select_by_type_name(ByVal conn As DBConnection, ByVal caseName As String) As Integer
    Dim sp As SqlCommand = conn.GetStoredProcedure("intake_types_select_by_type_name")
    sp.Parameters.Add("@type_name", SqlDbType.VarChar, 100).Value = caseName

    Dim result As Object = sp.ExecuteScalar()

    If result Is Nothing Then
        Return -1 ' check for -1 in the calling code '
    Else
        Return CInt(result)
    End If

End Function

It is almost always good idea to specify the size of the SQL parameter so that the database can re-use the execution plan rather than making a new execution plan for each length of the parameters.

Please note that it is generally a bad idea to use one SQL connection for everything: you should use the connection then dispose of it as soon as its immediate use is done. It seems like a good idea at first, but it is fighting against the way that it is designed (connection pooling takes care of fast re-opening of connections).

Ref: Execute Scalar to trap error in case of no records returned.