How can I add default values to a parameterized query

When using stored procedures I am able to add default values to parameters, for example:

@name varchar(50) = NULL

Is is possible to do something similar when using a parameterized query in .NET开发者_运维问答.

Thanks

Not sure what language you are using but I often handle this via overloads. I do this because it centralizes the defaults in one place.:

//call this method if you have no value for name
void MyMethod()
{
    string name = null;
    MyMethod(name);
}

//call this when you want to assign a value to name
void MyMethod(string name)
{
    //your normal code here where name is assigned as parameter value
}

About the only way I can think of is (Warning, ugly code ahead!):

SELECT column1, column2
FROM tabl1
WHERE column1 = ISNULL(@column1Parameter, column1)

The other option is to have a series of IF/ELSE blocks in your C#/VB.net code such as:

public void MethodThatCallsParameterisedSql(string column1Value)
{
    var command = new SqlCommand(PARAMETERISED_SQL_CONSTANT);
    if (string.IsNullOrEmpty(column1Value))
    {
        command.Parameters.Add("@column1Value", DEFAULT_VALUE_FOR_COLUMN1);
    }
    else
    {
        command.Parameters.Add("@column1Value", column1Value);
    }
}

myCommand.SelectCommand.Parameters.Add("@name", SqlDbType.VarChar, 50);
if (Name == NULL)
  myCommand.SelectCommand.Parameters["@name"].Value = NAME_DEFAULT_VALUE;
else
  myCommand.SelectCommand.Parameters["@name"].Value = Name;

Simply don't set it?

if (!string.IsNullOrEmpty(column1Value))
    {
        command.Parameters.Add("@column1", column1Value);
    }

This is what we do for optional parameters for stored procs

This may not work for some providers that only use ordinal parameters rather then named parameters. SQL Server/.net uses named parameters

You can use DBNull.Value for giving default value. For example:

if(string.IsNullOrEmpty(name)){
     //set default value
     command.Parameters.Add("@name", DbType.String, 50).Value = DBNull.Value;
}
else
{
    //set your parameter value
    command.Parameters.Add("@name", DbType.String, 50).Value = name;
}