If I am using data reader then how to deal with return value of stored procedure?

Question

Is there any method to Debug a Stored Procedure step by step?

Answer 1

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