How to Use Default Parameters in User Defined Functions in SQL Server 2005

Yesterday I had to add a much needed parameter to a SQL Server 2005 user-defined function (or UDF). The catch was that the current version of the UDF (i.e. the version without the parameter) was already being used in production by a few SQL queries. Not wanting to break those existing queries, I decided to assign a sensible default value to the new parameter. With a default value I figured I could add the parameter to the UDF without having to modify existing calls to the UDF.

Boy was I wrong.

Let me explain by using a simple, contrived “hello world” example. Say you want to create a UDF that takes in a string, appends it to the end of the string “Hello “, and returns the concatenated string. Furthermore, if the user chooses not to pass in a string, the UDF uses the default value “World”, resulting in a return value of “Hello World”.

Here is one possible definition of the UDF:

CREATE FUNCTION dbo.sayHello
(
   @who varchar(50) = 'World'
)
RETURNS varchar(100)
AS
BEGIN
RETURN 'Hello ' + @who;
END

In the above code, @who is the parameter and I have assigned it a default value by appending = 'World' to the parameter definition. Here is how you invoke it with a parameter value:

select dbo.sayHello('Joe')

The above invocation would produce the following output:

Hello Joe

Now it’s time to try invoking the UDF without a parameter, so that the default is used. Based on my experience with other languages such as C++, I figured I could invoke the UDF without any parameter values, like this:

select dbo.sayHello()

Unfortunately, I got the following error message when I invoked the UDF without a parameter value:

Msg 313, Level 16, State 2, Line 1
An insufficient number of arguments were supplied for the
procedure or function dbo.sayHello.

WTF? A quick search of the SQL Server 2005 Books Online revealed the problem and the solution. According to the “Creating User-defined Functions” section of Books Online:

When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value.

The solution was to call the UDF like this (note the keyword DEFAULT):

select dbo.sayHello(DEFAULT)

That invocation produced this output, which uses the default value of the parameter:

Hello World

I wish I didn’t have to supply the word DEFAULT because that necessitated having to update all of the queries that called my UDF. It’s doable but it’s a pain in the butt. I wish SQL Server 2005 didn’t have this restriction (and as far as I can tell, SQL Server 2008 continues to have this restriction).

Another nice T-SQL option would be the ability to overload functions like you can in object-oriented languages such as Java. One can dream, I suppose.