optional parameters in stored procedure

Question

I am building a website using mvc and want to save some data using a stored procedure that contains a set of parameters. I was wondering if it's possible to set some of those parameters as optional. The reason being that the data that gets sent back depends on a type of account. If the user edits account type 1, then all parameters must be sent back, if they edit account type 2, then only 3 parameters get sent back.

sql parameters:

alter PROCEDURE web.Maint_UpdateClinic
       @AccountID INT
       ,@IsActive BIT = 1
       ,@AccountName VARCHAR(100) = NULL
       ,@AccountAddress VARCHAR(100) = NULL
       ,@City VARCHAR(100) = NULL
       ,@State VARCHAR(2) = NULL
       ,@ZipCode VARCHAR(10) = NULL
       ,@PhoneNumber VARCHAR(20) = NULL
       ,@WebID INT

action in controller calling the procedure:

using (OdbcConnection _conn = new OdbcConnection("FILEDSN=c:\\datasources\\RxCard.dsn"))
            using (OdbcCommand cmd1 = new OdbcCommand())
            {
                cmd1.Connection = _conn;
                cmd1.CommandText = "{call web.Maint_UpdateClinic(?,?,?,?,?,?,?,?,?)}";
                cmd1.Parameters.AddWithValue("@AccountID", AccountID);
                cmd1.Parameters.AddWithValue("@IsActive", true);
                cmd1.Parameters.AddWithValue("@AccountName", AccountName);
                cmd1.Parameters.AddWithValue("@Address", Address);
                cmd1.Parameters.AddWithValue("@City", City);
                cmd1.Parameters.AddWithValue("@State", State);
                cmd1.Parameters.AddWithValue("@ZipCode", ZipCode);
                cmd1.Parameters.AddWithValue("@PhoneNumber", PhoneNumber);
                cmd1.Parameters.AddWithValue("@WebID", CookieStore.GetCookie("WebId"));            
                cmd1.CommandType = CommandType.StoredProcedure;
                _conn.Open();
                cmd1.ExecuteNonQuery();
                _conn.Close();
            }

The `CommandText` should just be the name of the SP if `CommandType` is `StoredProcedure`. Then you can add as many or as few parameters as you want (but the names must match the SP names). http://stackoverflow.com/a/7542564/123422 — Paul Abbott, Jul 15 '16 at 22:57
I would suggest you pass all the arguments with required parameters and pass the optional parameters as null, In your SP check what parameters are not null and based on that update your table. — Saket Choubey, Jul 15 '16 at 23:01
As extra explanation to @PaulAbbott's comment: The parameters that have a default value of NULL in your stored proc (IsActive, AccountName, etc.), may or may not be specified when calling the sproc. So you can skip calling cmd1.Parameters.AddWithValue for values you don't have. — Turbo, Jul 15 '16 at 23:36
I suggest changing the title of your question. You clearly understand how to create optional parameters, so that's not what you're actually asking about. — Scott Hannen, Jul 16 '16 at 02:15
This is actually not my code but now I understand how this action works. At one point when I was running this, I would get a runtime error letting me know the "Procedure expects '@parameter', which was not supplied." I don't recall exactly what resolved that issue but now it works just fine. Thanks for the clear explanations! — thatdude, Jul 18 '16 at 17:14

score 3 · Accepted Answer · answered Jul 16 '16 at 02:30

If the parameters are optional in the stored procedure then you can choose whether to add them. For example, if you remove this line (above)

cmd1.Parameters.AddWithValue("@AccountName", AccountName);

then the stored procedure will use the default value of @AccountName.

That means you just need a way to determine whether or not to pass each parameter. How you do that is tougher to answer because it depends on the behavior of your application.

You could, for example, do this:

if(AccountName != null)
    cmd1.Parameters.AddWithValue("@AccountName", AccountName);

In other words, if a value wasn't provided for AccountName then don't send one to the procedure. That works as long as you don't need to be able to set AccountName to null using this method.

You could pass a parameter to the method indicating what the account type is, and use that to determine which parameters to add and which to leave out.

I noticed that you have separate variables for each value (Account, Address, etc.) It might be easier to work with if you put them into a class, like

public class ClinicUpdate
{
    public string AccountId {get;set;}
    public string Address {get;set;}
    //etc
}

That can make it a lot easier if you decide to rearrange your code. For example, you might decide to make two methods - one that adds the parameters for one type of account, and another to add parameters for the other type. That's a lot easier if you're passing around a single object instead of a big list of parameters.
It's good practice anyway. Opinions vary but I recommend a maximum of four parameters per method call. If you find yourself passing a similar list of parameters to many methods then you probably need a class for them.

optional parameters in stored procedure

1 Answers1