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

Question

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?

Best solution I've seen so-far is to avoid using the default clauses in the TVF parameters definition, pass in nulls as Kevin suggested, and manually create defaults in your TVF body. Here's an article I found (it's about Sprocs, but the problem is the same). http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/3825fe1c-7b7e-4642-826b-ea024804f807 As with all other things associated with Microsoft, getting this to work will involve a half-baked workaround. — Mason, Dec 02 '09 at 22:43

score 3 · Answer 1 · edited May 07 '12 at 13:57

3

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

edited May 07 '12 at 13:57

Michał Powaga

22,561
8
51
62

answered Nov 01 '10 at 08:41

Mohamed Aslam

31
2

Additionally, it seems (from my testing so far) you don't even have to specify the datatype; so you can say: command.Parameters.Add(new SqlParameter("@OptionalParam", null)); ...and the [null] will be interpreted as [default] – tbone Jul 08 '15 at 20:48
1

EDIT: my prior comment is incorrect: [null] is **not** interpreted as [default] – tbone Jul 08 '15 at 21:23
Ok, after even more testing, at least with the current framework version I am using, adding the parameter without specifying a value does NOT result in the default value being used. Calling from SSMS with [default] does not yield the same result as passing the parameter with no value, even though that is the implied behavior here: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.value%28v=vs.110%29.aspx – tbone Jul 08 '15 at 22:44
Or in other words....I think this answer ("You don't have to add above code (The optional parameter) for default.") is not correct - or at least not always. – tbone Jul 09 '15 at 04:47

score 1 · Answer 2 · answered Dec 06 '12 at 06:39

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

score 0 · Answer 3 · edited Apr 25 '23 at 14:58

0

You can pass Null as the parameter value.

This article shows examples.

edited Apr 25 '23 at 14:58

General Grievance

4,555
31
31
45

answered Dec 02 '09 at 21:39

Kevin Buchan

2,790
3
27
34

Thanks I'll try that, but I'm fairly certain that passing an explicit null will just override the defaults defined in the tvf definition, making the defaults useless. – Mason Dec 02 '09 at 21:42
I'd love to hear your findings. I'm amazed that I've never needed to know this before seeing your question. I would still expect Null to work and that DBNull would override the paramater's definition. – Kevin Buchan Dec 02 '09 at 21:46
Yeah I just checked, that's no good. And the reason is because there's a difference between: select * from fn_SampleTVF(123, DEFAULT) and select * from fn_SampleTVF(123, null) Thanks though. – Mason Dec 02 '09 at 21:47
P.S. The problem is that you're actually right. DBNull does indeed override the TVF's parameter definition. What I'm looking to do is USE the TVF's parameter definition. – Mason Dec 02 '09 at 21:49

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

3 Answers3