CODEWITHSUNDEEP
ado.netuser-defined-functionssqlparameter
Mason
Mason

Reputation: 147

How to pass default keyword for table valued function call in ADO.NET

So here's the deal. In our database, we wrap most of our reads (i.e. select statements) in table valued functions for purposes of security and modularity. So I've got a TVF which defines one or more optional parameters.

I believe having a TVF with defaulted parameters mandates the use of the keyword default when calling the TVF like so:

select * from fn_SampleTVF(123, DEFAULT, DEFAULT)

That's fine, everything works in the query analyzer, but when it comes time to actually make this request from ADO.NET, I'm not sure how to create a sql parameter that actually puts the word default into the rendered sql.

I have something roughly like this now:

String qry = "select * from fn_SampleTVF(@requiredParam, @optionalParam)";

DbCommand command = this.CreateStoreCommand(qry, CommandType.Text);

SqlParameter someRequiredParam = new SqlParameter("@requiredParam", SqlDbType.Int);
someRequiredParam.Value = 123;
command.Parameters.Add(someRequiredParam);

SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int);
optionalParam.Value = >>>> WTF? <<<<
command.Parameters.Add(optionalParam);

So, anybody got any ideas how to pass default to the TVF?

Upvotes: 5

Views: 1240

Answers (3)

Kevin Buchan
Kevin Buchan

Reputation: 2860

You can pass Null as the parameter value.

This article shows examples.

Upvotes: 0

Andrey Morozov
Andrey Morozov

Reputation: 7979

I would have done so:

public void YourMethod(int rparam, int? oparam = null)
{
    String qry = string.Format("select * from fn_SampleTVF(@requiredParam, {0})"
        , !oparam.HasValue ? "default" : "@optionalParam");

    SqlParameter someRequiredParam = new SqlParameter("@requiredParam", SqlDbType.Int);
    someRequiredParam.Value = rparam;
    command.Parameters.Add(someRequiredParam);

    if (oparam.HasValue)
    {
        SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int);
        optionalParam.Value = oparam.Value;
        command.Parameters.Add(optionalParam);
    }
}

Upvotes: 1

Mohamed Aslam
Mohamed Aslam

Reputation: 31

SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int);
optionalParam.Value = >>>> WTF? <<<<
command.Parameters.Add(optionalParam);

You don't have to add above code (The optional parameter) for default. SQL Server will use the default as defined in your UDF. However if you would like to pass different value then you can pass:

SqlParameter optionalParam = new SqlParameter("@optionalParam", SqlDbType.Int); 
optionalParam.Value = newValue; 
command.Parameters.Add(optionalParam);

Upvotes: 3

Related Questions