Alter a SQL server function to accept new optional parameter

Question

I already have a function in SQL Server 2005 as:

ALTER function [dbo].[fCalculateEstimateDate] (@vWorkOrderID numeric) Returns varchar(100)  AS Begin   <Function Body> End 

I want to modify this function to accept addition optional parameter @ToDate. I am going to add logic in function if @Todate Provided then do something else continue with existing code.

I modified the function as:

ALTER function [dbo].[fCalculateEstimateDate] (@vWorkOrderID numeric,@ToDate DateTime=null) Returns varchar(100)  AS Begin   <Function Body> End 

Now I can call function as:

SELECT dbo.fCalculateEstimateDate(647,GETDATE()) 

But it gives error on following call:

SELECT dbo.fCalculateEstimateDate(647) 

as

An insufficient number of arguments were supplied for the procedure or function dbo.fCalculateEstimateDate.

which as per my understanding should not happen.

Am I missing anything? Thanks in advance.

Answer 1

From CREATE FUNCTION:

When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called 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.

So you need to do:

SELECT dbo.fCalculateEstimateDate(647,DEFAULT) 

Answer 2

The way to keep SELECT dbo.fCalculateEstimateDate(647) call working is:

ALTER function [dbo].[fCalculateEstimateDate] (@vWorkOrderID numeric) Returns varchar(100)  AS    Declare @Result varchar(100)    SELECT @Result = [dbo].[fCalculateEstimateDate_v2] (@vWorkOrderID,DEFAULT)    Return @Result Begin End  CREATE function [dbo].[fCalculateEstimateDate_v2] (@vWorkOrderID numeric,@ToDate DateTime=null) Returns varchar(100)  AS Begin   <Function Body> End