Modify the SQL server function to accept a new optional parameter

Question

I already have a function in SQL Server 2005:

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

I want to change this function to accept the optional @ToDate extra parameter. I am going to add logic to a function if @Todate Provided, and then do something else with existing code.

I changed the function as:

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

Now I can call the function as:

 SELECT dbo.fCalculateEstimateDate(647,GETDATE()) 

But it gives an error on the following call:

 SELECT dbo.fCalculateEstimateDate(647) 

but

Not enough arguments were provided for the procedure or function dbo.fCalculateEstimateDate.

which, in my understanding, should not occur.

Did I miss something? Thanks in advance.

Answer 1

From CREATE FUNCTION :

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

So you need to do:

 SELECT dbo.fCalculateEstimateDate(647,DEFAULT) 

Answer 2

The way to save SELECT dbo.fCalculateEstimateDate(647) :

 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 

Answer 3

I found the EXECUTE command as suggested here by T-SQL - a function with default parameters to work well. With this approach, there is no need for "DEFAULT" when you call a function, you just omit the parameter in the same way as with the stored procedure.