How to identify SQL Server stored procedure parmers at run time in .NET?

Question

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

Answer 1

ADO.Net has this functionality. Do you want to use

SqlCommandBuilder.DeriveParameters(mySqlCommand)

This will populate your SqlCommand object, "mySqlCommand", with the name and type of parameters. However, this requires an additional call to the database.

There are several walkthroughs on the Internet if you are advertising them. 4Guys has one here .

Answer 2

no, but you can get it from information_schema.parameters

Procedure example

 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 sizes from CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE

Answer 3

Yes, you can use 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); } }