Is there a way to determine if a parameter in a stored proc has a default value (and thus not required) in code - .Net?

Question

I am already pulling the parameters from the stored proc sent in like this:

 foreach (SqlParameter param in cmd.Parameters)
            {
               if ((param.Direction == ParameterDirection.Input) || (param.Direction == ParameterDirection.InputOutput))
                {
                    jsonReturn += "{\"paramName\":\"" + param.ParameterName + "\", \"paramType\":\"" + param.SqlDbType.ToString() + "\"},";
                }
            }

I looked into the SqlParameter object and could not find a way to see if it could tell me whether the Parameter had a default value... (although my debugger is acting weird, so who knows).

What I am doing is building a sort of Stored Proc helper for users.. I currently tell them all the parameters that belong to the Stored Proc they pick.... I WOULD REALLY like to be able to tell whether they are required.

Answer 1

To directly answer your question, no there is (probably) no way to determine if a stored procedure parameter possesses a default value 'in code' (i.e. using the SqlParameter class).

In SQL Server (at least SQL Server 2005), you can query the system catalog view sys.parameters (and join it to the catalog view sys.procedures) and evaluate the value of the column has_default_value.