Calling a parameterized stored procedure using ODBC

In .NET, executing a parameterized stored procedure using ODBC Provider is different from executing the procedure using SQL or OLE DB Provider. ODBC provider processes parameters by ordinal position (zero-based) and not by name. We should use the ODBC CALL syntax instead of using just the stored procedure name.

Syntax:

{Call <ProcedureName>}

The above syntax calls a procedure which doesn't require any parameters.
Ex:

OdbcCommand ODBCCommand = new OdbcCommand("{call GetUsers}", ODBCConnection);
ODBCCommand.CommandType = CommandType.StoredProcedure;

For calling a procedure which accepts one parameter the syntax will be

{CALL <ProcedureName> (?)}

Ex:

OdbcCommand ODBCCommand = new OdbcCommand("{call GetUser(?)}", ODBCConnection);
ODBCCommand.CommandType = CommandType.StoredProcedure;
ODBCCommand.Parameters.AddWithValue("@userId", 1234);

Following is the syntax for a procedure that return value. The first placeholder represents the return value

{? = CALL <ProcedureName> (?, ?)

Happy Coding 😊!!

Gopikrishna