How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?
Solution 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.
Solution 2
AFAIK, the only way to tell SQL Server to use a default value is via the DEFAULT
keyword or to exclude it from parameter list. That means that the use of the DEFAULT
keyword must be in your parameterized SQL Statement. So, something like:
Select ...
From dbo.udf_Foo( DEFAULT, @Param2, @Param3, DEFAULT, .... )
I suppose another approach would be to query the system catalogs for the actual value of the various DEFAULT
values and determine whether to set the SqlParameter to the default value that way, but that requires a convoluted second query to get the default values.
Solution 3
If you have the following function (for example):
CREATE FUNCTION dbo.UFN_SAMPLE_FUNCTION
(
@Param1 nvarchar(10),
@Param2 int = NULL
)
RETURNS TABLE
AS
RETURN
SELECT @Param1 AS Col1, @Param2 AS Col2;
GO
Then you can use it the following way (option 1):
SELECT * FROM dbo.UFN_SAMPLE_FUNCTION ('ABC', DEFAULT);
which is correct way and you get the following result:
Col1 Col2
---------- -----------
ABC NULL
But if you try to use parametrized query (option 2):
exec sp_executesql N'SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)',N'@P1 nvarchar(10),@P2 int',@P1=N'abc',@P2=default;
you will get an error:
Msg 8178, Level 16, State 1, Line 0
The parameterized query '(@P1 nvarchar(10),@P2 int)SELECT * FROM dbo.UFN_SAMPLE_FUNCTION' expects the parameter '@P2', which was not supplied.
If you have the following .net code:
public void RunTVF(string param1, int? param2)
{
using (SqlConnection con = GetProdConection())
{
using (var cmd = new SqlCommand("SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)", con))
{
cmd.CommandType = CommandType.Text;
var param = new SqlParameter
{
ParameterName = "@P1",
SqlDbType = SqlDbType.NVarChar,
Size = 10 ,
Value = param1
};
cmd.Parameters.Add(param);
param = new SqlParameter
{
ParameterName = "@P2",
SqlDbType = SqlDbType.Int,
Value = param2
};
cmd.Parameters.Add(param);
cmd.Connection.Open();
using (IDataReader dataReader = cmd.ExecuteReader())
{
//...
}
}
}
}
then, in case param2 = null as Jack suggested above, the script produced by the code will be identical to the option 2 and will result to the same error. So you cannot use NULL in this case.You cannot set DEFAULT as the value of SQLParameter either.
What you can do is to create a stored procedure to wrap the call to your funcion and move your default value from the function to the SP. Example:
CREATE PROCEDURE dbo.USP_SAMPLE_PROCEDURE
(
@Param1 nvarchar(10),
@Param2 int = NULL, --DEFAULT value now is here (remove it from the function)
@Statement nvarchar(max)
)
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_executesql @Statement,N'@P1 nvarchar(10),@P2 int',@P1=@Param1,@P2=@Param2;
END
The .NET code will look the following way:
public void RunWrapper(string param1, int? param2)
{
using (SqlConnection con = GetProdConection())
{
using (var cmd = new SqlCommand("USP_SAMPLE_PROCEDURE", con))
{
cmd.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter
{
ParameterName = "@Param1",
SqlDbType = SqlDbType.NVarChar,
Size = 10,
Value = param1
};
cmd.Parameters.Add(param);
param = new SqlParameter
{
ParameterName = "@Param2",
SqlDbType = SqlDbType.Int,
Value = param2
};
cmd.Parameters.Add(param);
param = new SqlParameter
{
ParameterName = "@Statement",
SqlDbType = SqlDbType.NVarChar,
Size = -1, //-1 used in case you need to specify nvarchar(MAX)
Value = "SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)"
};
cmd.Parameters.Add(param);
cmd.Connection.Open();
using (IDataReader dataReader = cmd.ExecuteReader())
{
//...
}
}
}
}
In this case null as a value for the param2 will be translated to the correct DEFAULT and the following script will be produced:
exec USP_SAMPLE_PROCEDURE @Param1=N'ABC',@Param2=default,@Statement=N'SELECT * FROM dbo.UFN_SAMPLE_FUNCTION (@P1, @P2)'
which will give you the following result:
Col1 Col2
---------- -----------
ABC NULL
I am not sure that this is the best practice. This is just the work-around.
Solution 4
Though you can't set an SQL keyword as the value of a parameter, you could in this case go and get the DEFAULT VALUE.
SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name'"
Related videos on Youtube
Llyle
Have a burning passion for Software Development and its accompanying principles and practices. My interest currently lies with ASP.NET MVC, NHibernate, and more recently Castle Windsor.
Updated on November 13, 2020Comments
-
Llyle over 3 years
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 toSqlCommand
.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 setDEFAULT
as the value of mySQLParameter
.Can I? What is best practice in such an instance?
-
OMG Ponies about 14 yearsSQL "DEFAULT" keyword? There're default constraints, which allow you to set a default value in the case that one isn't provided for the column...
-
Bill Karwin about 14 years@OMG Ponies: You can use
DEFAULT
in anINSERT
(orUPDATE
) statement just like you can useNULL
:INSERT INTO foo(col1, col2) VALUES(1234, DEFAULT)
. This is standard SQL but little-known. -
OMG Ponies about 14 years@Bill Karwin: Thx, definitely news to me.
-
Thomas about 14 yearsIt is even possible to pass
DEFAULT
to a TVF. However, SQL does not appear to like usingDEFAULT
in function that you passed to TVF (i.e.Coalesce(@Foo, DEFAULT)
). -
DanMan about 10 yearsThis might interest you: stackoverflow.com/questions/2464535/…
-
-
Llyle about 14 yearsYeah, its a bummer. I understand why but just makes things a little sticky for me :)
-
Solomon Rutzky almost 10 years-1 This is not true. When calling a stored procedure in the context of
SqlCommand.CommandType = CommandType.Text
, you can indicate that a parameter that has a default value should actually use that default by either not defining the parameter, not setting it, or setting it tonull
as inSqlParameter.Value = null;
. But this is not the same as using the T-SQLDEFAULT
(which is what this question is about). It is the same as calling the proc without the param. And this question is specific to TVFs, not stored procedures, and TVFs do not allow for not specifying all of the input params. -
Jalal about 9 yearsAlso in stored procedures if you set
SqlParameter.Value
tonull
, it automatically sentdefault
value to SQL Server. If you want to set database field tonull
useDBNull.Value
. -
tbone almost 9 yearsThe problem with this is it assumes that [null] implies [default], which may not always be the case. This problem is a real shortcoming in Microsoft's implementation.
-
tbone almost 9 yearsDo you know if it is possible to pass DEFAULT via a Parameter to a TVF call? My research indicates no, it is not possible, you must detect no value being specified and manually replace the @ParameterName in the SqlText itself with DEFAULT. That's my current belief anyways.
-
tbone almost 9 years@srutzky That was to you
-
MgSam almost 5 yearsA default value can be a SQL Expression, including calling a function. Thus, this isn't generally useful on the .NET side.
-
Chris Catignani almost 5 years@MgSam Not totally...this gets the DEFAULT value form the system catalogue...which can then be passed as a parameter...not elegant but sticking with the OP's original question. Or are we talking about different things?