Get stored procedure parameters by either C# or SQL?

Question

Hello, I was hoping to find an easy way to get a parameter list of a storec procedures parameters. If the proc has 3 params, I want a list like that:

param1
param2
param3

It would be best to be able to do that in C# Code, but SQL would suffice as well. Ideas?

Thanks!! :-)

Answer 1

If you're using ADO.NET then this might help: SqlCommandBuilder.DeriveParameters - Get Parameter Information for a Stored Procedure - ADO.NET Tutorials

Answer 2

If you're familiar with Enterprise Library, there's a good method which allows to DiscoverParameters(), using the Data Access Application Block.

DbCommand command = new DbCommand();
command.CommandText = @"myStoredProc";
command.CommandType = CommandType.StoredProcedure;

Database database = new SqlDatabase(myConnectionString);
database.DiscoverParameters(command);
// ...

Some links that might help:

1. DiscoverParameters Method;
2. Microsoft.Practices.EnterpriseLibrary.Data Namespace.

The above links refers to EntLib 3.1. Depending on the .NET Framework version you're using, you might also consider downloading the correct EntLib version for you following this link.

Answer 3

select * from information_schema.parameters
where specific_name='your_procedure_name'

Answer 4

For SQL Server this should work.

private void ListParms()
{
    SqlConnection conn = new SqlConnection("my sql command string");
    SqlCommand cmd = new SqlCommand("proc name", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    conn.Open();
    SqlCommandBuilder.DeriveParameters(cmd);


    foreach (SqlParameter p in cmd.Parameters)
    {
       Console.WriteLine(p.ParameterName);
    }


}