Call stored procedure with an output parameter using ExecuteDataset

Question

I need to call a stored procedure in a MySQL (5.7) database from a VB.NET application. Said procedure makes a SELECT statement at the end which I need to retrieve in my app as a DataSet. Previously it worked fine, but I added an output parameter to the stored procedure, and I get the following error when I call it:

{"Incorrect number of arguments for PROCEDURE my_database.SP_MY_PROCEDURE; expected 3, got 2"}

Here's my current VB.NET code:

Public Shared Function CallStoredProcedure(ByVal stringParam As String,
                                           ByVal intParam As Integer) As DataSet
    Try
        Dim ds As New DataSet

        Dim params As New List(Of MySqlParameter)
        Dim arrayParams() As MySqlParameter

        params.Add(New MySqlParameter("@param1", stringParam))
        params.Add(New MySqlParameter("@param2", intParam))

        arrayParams = params.ToArray()

        ds = MySqlHelper.ExecuteDataset(connString,
                                        "CALL SP_MY_PROCEDURE(@param1, @param2);",
                                        arrayParams)

        Return ds
    Catch objException As Exception
        Return Nothing
    End Try
End Function

I tried adding a third parameter to the CALL statement in the string, like this:

ds = MySqlHelper.ExecuteDataset(connString,
                                "CALL SP_MY_PROCEDURE(@param1, @param2, param3);",
                                arrayParams)

but that just returns a different error:

{"OUT or INOUT argument 3 for routine gw_my_database.SP_MY_PROCEDURE is not a variable or NEW pseudo-variable in BEFORE trigger"}

How can I accomplish this? Maybe ExecuteDataset can't be used with output parameters; at least I haven't found examples or anything. It that's the case, what's a good alternative?

Answer 1

You can definitely use ExecuteDataset() with output parameter. You need to make sure all the mandatory parameters are added to your parameter list including output parameter. You need to confirm the type and size where applicable and set the direction of each parameter correctly. After executing ExecuteDataset() you can retrieve the value from our output parameter.

I could not see adding the third parameter in your code (or perhaps you haven't showed that part), setting direction/type/size.