How to deal with return value of stored procedure when you are using DataReader ?

Answer:

To deal with such scenario, you can find “ParameterDirection.ReturnValue”.
You can use this parameter direction to read the return value of the stored procedure.
Following example can make it more clearer.
In case, you have following or similar type of return statement somewhere in your stored procedure.

Return -999

To read this value, following code works,

Dim result As Integer = 0

Dim retValParam As New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)

        retValParam.Direction = ParameterDirection.ReturnValue

        dbCommand.Parameters.Add(retValParam)

        database.ExecuteNonQuery(dbCommand)

        result = retValParam.Value ‘ here the result must contain the returned value. In this case, it will be -999

Thanks,
Bhakti Shah