更改 SQL 服务器函数以接受新的可选参数

我在 SQLServer2005中已经有一个函数:

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

我想修改这个函数来接受附加的可选参数@ToDate。如果@Todate 提供了函数，我将在函数中添加逻辑，然后对现有代码进行其他操作。

我将函数修改为:

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

现在我可以调用如下函数:

SELECT dbo.fCalculateEstimateDate(647,GETDATE())

但在以下情况下，它出现了错误:

SELECT dbo.fCalculateEstimateDate(647)

作为

为过程提供的参数数量不足，或者函数 dbo.fCalculateEstimateDate。

据我所知，这是不应该发生的。

我错过什么了吗？先谢谢你。

117687 次浏览

小开

最佳答案

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)

小开

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.