How can I retrieve a list of parameters from a stored procedure in SQL Server

Question

Using C# and System.Data.SqlClient, is there a way to retrieve a list of parameters that belong to a stored procedure on a SQL Server before I actually execute it?

I have an a "multi-environment" scenario where there are multiple versions of the same database schema. Examples of environments might be "Development", "Staging", & "Production". "Development" is going to have one version of the stored procedure and "Staging" is going to have another.

All I want to do is validate that a parameter is going to be there before passing it a value and calling the stored procedure. Avoiding that SqlException rather than having to catch it is a plus for me.

Joshua

Answer 1

You want the SqlCommandBuilder.DeriveParameters(SqlCommand) method. Note that it requires an additional round trip to the database, so it is a somewhat significant performance hit. You should consider caching the results.

An example call:

using (SqlConnection conn = new SqlConnection(CONNSTRING))
using (SqlCommand cmd = new SqlCommand("StoredProc", conn)) {
   cmd.CommandType = CommandType.StoredProcedure;
   SqlCommandBuilder.DeriveParameters(cmd);

   cmd.Parameters["param1"].Value = "12345";

   // ....
}