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 )

Output

————-

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 )

Output

————-

1

There you have it, works like a charm