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.
This question is related to
sql
sql-server-2005
From CREATE FUNCTION:
When a parameter of the function has a default value, the keyword
DEFAULTmust 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)
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
I have found the EXECUTE command as suggested here T-SQL - function with default parameters to work well. With this approach there is no 'DEFAULT' needed when calling the function, you just omit the parameter as you would with a stored procedure.
Source: Stackoverflow.com