CODEWITHSUNDEEP
c#sql-serverstored-procedures
thatdude
thatdude

Reputation: 77

optional parameters in stored procedure

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();
            }

Upvotes: 0

Views: 7365

Answers (1)

Scott Hannen
Scott Hannen

Reputation: 29207

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.

Upvotes: 3

Related Questions