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?
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/
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);
}
}
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;
}
}
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:
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.
