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?

score 138 · Accepted Answer · answered Oct 04 '12 at 07:07

138

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)

answered Oct 04 '12 at 07:07

Damien_The_Unbeliever

234,701
27
340
448

3

Is there any way to keep SELECT dbo.fCalculateEstimateDate(647) call working?? – MaxRecursion Oct 04 '12 at 07:11
39

No. Each call to the function must have the same number of parameters specified. You could always create a *new* function with two parameters, and turn `dbo.fCalculateEstimateDate` into a wrapper function that simply adds the second parameter to call the new function. – Damien_The_Unbeliever Oct 04 '12 at 07:13
10

why on earth does this differ from Stored procedures? Im pretty sure you can just skip the second parameter and it would just work! – Peter Feb 04 '15 at 14:36
8

@Peter - T-SQL is a *very* crufty language. With lots of warts in odd and surprising places. I can't give you an actual reason for the different, but since stored procedures always appear in standalone contexts rather than as part of larger statements, it's may be parse related. – Damien_The_Unbeliever Feb 04 '15 at 14:40
8

@Peter it was written by people with no common sense or db using experience – dvdmn Sep 08 '16 at 01:51
1

@Peter What I think because we can't call function in SQL by setting its parameters values like Stored Procedure. Hence it can't be decided that, which parameters to ignore by function. – Zeeshanef Nov 12 '20 at 13:01
1

@Peter "I am pretty sure" is different to "I read the documentation and I tried it". Accept it. Default parameters are handled differently in procedures and functions. – SQL Police Oct 14 '22 at 08:45

score 30 · Answer 2 · edited May 06 '15 at 15:24

30

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

edited May 06 '15 at 15:24

Politank-Z

3,653
3
24
28

answered May 06 '15 at 15:05

Gus

301
3
2

3

Can't you just say `RETURN [dbo].[fCalculateEstimateDate_v2] (@vWorkOrderID,DEFAULT)` ? – Derek Tomes May 07 '15 at 01:26
Yes! sure. It´s the same – Gus May 07 '15 at 15:03

score 2 · Answer 3 · edited May 23 '17 at 12:02

2

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.

edited May 23 '17 at 12:02

Community

1
1

answered Oct 03 '16 at 20:42

Learning2Code

521
9
21

If you're calling a UDF from within another query (e.g. a scalar function in a `SELECT`, or a table-valued function in a `FROM` or `JOIN`) then you can't use `EXECUTE` btw. But what's wrong with using the `DEFAULT` keyword? I really can't think of any good reason to not use it, and your function has too many optional parameters then you should refactor it into differently-named functions, imo. – Dai Jul 16 '21 at 22:39

Alter a SQL server function to accept new optional parameter

3 Answers3

Linked

Related