nosuchblogger

Recently, I had to dig into some database code which executes a stored proc. The code was generic - it should work with any IDbConnection. It looks something like this:

using (IDbConnection conn = CreateConnection())
using (var command = conn.CreateCommand()) {
  command.CommandText = storedProcName;
  command.CommandType = CommandType.StoredProcedure;

  var param1 = command.CreateParameter();
  param1.ParameterName = "@InputText";
  param1.DbType = DbType.String;
  param1.Size = 200;
  param1.Direction = ParameterDirection.Input;
  param1.Value = "Hello World";
  command.Parameters.Add(param1);

  var output1 = command.CreateParameter();
  output1.ParameterName = "@OutputId";
  output1.DbType = DbType.Int32;
  output1.Direction = ParameterDirection.Output;
  command.Parameters.Add(output1);

  command.ExecuteScalar();

  var outputValue = output1.Value;
}

This was working well when using the System.Data.SqlClient provider. However, I wanted to connect to Sybase using ODBC. At this point I got the error:

Procedure {storedProcName} expects parameter @InputText, which was not supplied.

After much head scratching, found out that the problem is that ODBC doesn't support named parameters. The solution is to change the command to manually execute the stored proc - i.e. execute {storedProcName} ? ? like so:

command.CommandText = string.Format("execute {0} ? ?", storedProcName);
command.CommandType = CommandType.Text;

Problem solved.