Geek Answers Handbook

How to pass the default keyword to call a function with a table function in ADO.NET

So here's the deal. In our database, we transfer most of our readings (i.e., Select statement) to table functions for security and modularity purposes. Therefore, I have a TVF that defines one or more optional parameters.

I believe that having TVF with default parameters implies using the keyword defaultwhen calling TVF as follows:

select * from fn_SampleTVF(123, DEFAULT, DEFAULT)

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

Now I have something like this:

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

, - , default TVF?

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

( ) . SQL Server , UDF. , , :

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

:

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);
    }
}
+1

More articles:


All Articles