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    lt Function Body gt  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    lt Function Body gt  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

User · Accepted Answer

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

User · Answer

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

User · Answer

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    lt Function Body gt  End

[sql] Alter a SQL server function to accept new optional parameter

Examples related to sql

Examples related to sql-server-2005