sp_getProcedureColumns

Question

I have tried to pass in the proc name into procedureNamePattern and the procedure seems to return all the metadata for all the procedures. I pass in a empty string and I get the same results.

The following is the code used:

using (AdsCommand command = new AdsCommand { CommandText = "sp_getProcedureColumns", 
                                             Connection = connection, 
                                             CommandType = CommandType.StoredProcedure })
{
   AdsParameter param = new AdsParameter("@procedureNamePattern", DbType.String) { 
     Direction = ParameterDirection.Input, Value = originalProcedureName };
   command.Parameters.Add(param);

   AdsParameter param0 = new AdsParameter("@catalog", DbType.String) { 
       Direction = ParameterDirection.Input, Value = null };
   command.Parameters.Add(param0);

   AdsParameter param1 = new AdsParameter("@schemaPattern", DbType.String) { 
       Direction = ParameterDirection.Input, Value = null };
   command.Parameters.Add(param1);

   AdsParameter param2 = new AdsParameter("@columnNamePattern", DbType.String) { 
       Direction = ParameterDirection.Input, Value = null };
   command.Parameters.Add(param2);

Answer 1

With stored procedures, you can use the DeriveParameters method. That might make it simpler. The following is an example:

AdsCommand cmd = conn.CreateCommand();    
cmd.CommandText = "sp_getProcedureColumns";
cmd.CommandType = CommandType.StoredProcedure;
cmd.DeriveParameters();
cmd.Parameters["ProcedureNamePattern"].Value = "MyProcName";
AdsDataReader rdr = cmd.ExecuteReader();

If you do not call the DeriveParameters method, then the underlying SQL statement that is generated is produced directly from the provided parameters in the given order. That means you would need to provide the parameters in the order to match the procedure definition. In this case, the ProcedureNamePattern parameter needs to be 3rd. If you change the order of the cmd.Parameter.Add() calls, then your original example should work.