How to get required parameters from a stored procedure in C# [duplicate]

In SQL Server terms, a required parameter is any parameter that does not have a default value, e.g.:

CREATE PROCEDURE Foo
(
  @Required INT,
  @NotRequired INT = NULL
)

In this case, you can call the procedure without the @NotRequired parameter, but you can't call the procedure without the @Required one -- you'll get the error in your question.

You can use SqlCommandBuilder.DeriveParameters on a SqlCommand object you've set up to populate the Parameters collection, but that will not tell you the difference between optional and required parameters. You could to just assume that all stored procedure parameters are required, but that's a terrible idea. Stored procedures very often allow you to pass various combinations of parameters, and behave appropriately. For example, a "search" stored procedure may take a big list of parameters but only require you to specify the ones you really want to search for; or a stored procedure may have default values for certain parameter and only expect you to specify explicit values if you want to change the behavior.

To actually tell the difference, you need to use the SQL Server SMO API. This should be installed as part of the SQL Server client tools setup, you'll need to add a reference to several assemblies: Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk, and Microsoft.SqlServer.Smo.

From there, the basic idea is:

var server = new Server("instancename");
server.ConnectionContext.DatabaseName = "DatabaseName";
server.ConnectionContext.LoginSecure = true;
server.ConnectionContext.Connect();

var db = server.Databases["databasename"];
var proc = new StoredProcedure(db, "storedprocedurename");

foreach (StoredProcedureParameter parameter in sp.Parameters)
{
    if (parameter.DefaultValue != null)
    {
      // param is required.
    }
}

You can see more information in this answer and this question and this MSDN article. In particular, that second question talks about ways to speed up this process by telling SMO not to load some information you won't care about.