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?

Answer 1

You can pass Null as the parameter value.

This article shows examples: http://support.microsoft.com/kb/321902