How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?

Question

I have a parameterized SQL query targetted for SQL2005 which is dynamically created in code, so I used the ADO.NET SqlParameter class to add sql parameters to SqlCommand.

In the aforementioned SQL I select from a Table Valued Function with has defaults. I want my dynamic sql to sometimes specify a value for these default parameters, and other times I want to specify that the SQL DEFAULT - as defined in the Table Valued Function - should be used.

To keep the code clean I didn't want to dynamically add the SQL DEFAULT keyword and parameterize it when a non-default is to be used, I just wanted to set DEFAULT as the value of my SQLParameter.

Can I? What is best practice in such an instance?

Answer 1

SQL query parameters take the place of literal values only.

You can't send an SQL keyword as the value of a parameter, just as you cannot send a table identifier, column identifier, list of values (e.g. for an IN predicate), or an expression. The value of the parameter is always interpreted as a literal value, as if you had included a quoted string literal or a numeric literal in your query.

Sorry, but you have to include an SQL keyword as part of the SQL query before you prepare that query.