Someone had some trouble earlier today with calling a user defined function that has a default value for a parameter
When you have a stored procedure with default values for parameters, you can omit those when calling the proc. With user defined functions, it works a little different, let’s take a look.
First create this simple function
CREATE FUNCTION dbo.fnTest(@param1 INT, @param2 int = 1 ) RETURNS int AS BEGIN RETURN @param2 END GO
As you can see @param2 has a default of 1.
Calling the function by supplying both parameters works as expected
SELECT dbo.fnTest( 23,3 )
Now try to do this
SELECT dbo.fnTest( 23 )
Here is the error message that we get back
_Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the procedure or function dbo.fnTest._
If you look in books on line: http://msdn.microsoft.com/en-us/library/ms186755.aspx
You will see the following text
If a default value is defined, the function can be executed without specifying a value for that parameter.
When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called in order to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. An exception to this behavior is when invoking a scalar function by using the EXECUTE statement. When using EXECUTE, the DEFAULT keyword is not required.
So, let’s try that
SELECT dbo.fnTest( 23, default )
There you have it, works like a charm