Get stored procedure parameters by either C# or SQL?

Question

I was hoping to find an easy way to get a parameter list of a stored procedures parameters. If the procedure has 3 paramaters, I want a list like this:

param1
param2
param3

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

Solution 1

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

Also refer this post to know more methods https://exploresql.com/2016/10/14/different-methods-to-get-parameter-list-of-a-stored-procedure/

Solution 2

For SQL Server this should work.

private void ListParms()
{
    SqlConnection conn = new SqlConnection("my sql connection 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);
    }
}

Solution 3

You can do this without ever touching SqlConnection, which I find is a bonus.

This uses the SqlServer.Management.Smo namespace, so you need a reference to Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk, and Microsoft.SqlServer.Smo in your project.

Then use the following code:

Server srv = new Server("serverNameHere");
srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
srv.ConnectionContext.LoginSecure = false; //if using username/password
srv.ConnectionContext.Login = "username";
srv.ConnectionContext.Password = "password";
srv.ConnectionContext.Connect();

Database db = srv.Databases["databaseNameHere"];

foreach(StoredProcedure sp in db.StoredProcedures)
{
    foreach(var param in sp.Parameters)
    {
        string paramName = param.Name;
        var dataType = param.DataType;
        object defaultValue = param.DefaultValue;
    }
}

Solution 4

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.

View more solutions

Comments

[Moondustt]

I found these assemblies here: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies

[B. Clay Shannon-B. Crow Raven]

In my case I added this in my foreach: if (p.ParameterName == "@RETURN_VALUE") continue;