How to determine SQL Server stored procedure parmeters at run time in .NET?

Question

Is there any way for an application to determine the parameters and paramter types of a stored procedure at run time?

Answer 1

no, but you can get it from information_schema.parameters

example procedure

create procedure prtest
@id int,
@name varchar(200),
@value decimal(20,10)
as
select 'bla'

go

now run this query

select parameter_name,data_type,* from information_schema.parameters
where specific_name = 'prtest'
order by ordinal_position

output

@id int
@name   varchar
@value  decimal

you still need to get the sizes from CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION, NUMERIC_SCALE

Answer 2

ADO.Net has this functionality. You'll want to use

SqlCommandBuilder.DeriveParameters(mySqlCommand)

This will populate your SqlCommand object, "mySqlCommand", with the name and type of the parameters. However, it does require an extra call to the database.

There are a few walkthroughs on the web if you google around for them. 4Guys has one here.

Answer 3

Yes, you can by using the DeriveParameters method of the SqlCommandBuilder class...

using (var conn = new SqlConnection(myConnectionString))
using (var cmd = new SqlCommand("dbo.MyStoredProc", conn))
{
    conn.Open();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    SqlCommandBuilder.DeriveParameters(cmd);

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