SQL Server 中的可选参数 [英] Optional parameter in SQL server

问题描述

我有一个用户定义的函数，它在许多存储过程中使用，它会返回一些值.如果我可以添加一个新的可选参数.

I have a user defined function which is used in many stored procedures which will return me some value. If it possible for me to add a new optional parameter to the same.

如果我不传递任何值，它应该是空的，如果我传递一些值，它应该接受它.我不想去更改所有存储过程来这样做.

If I don't pass any value it should be null and if I pass some value it should take it. I don't want to go and change all the stored procedures to do so.

示例代码

dbo.CalculateAverageForUser(userid int)

我可以使用 dbo.CalculateAverageForUser(userid int, type NVARCHAR(10) = NULL)

推荐答案

如果您不想调整所有引用该函数的现有存储过程，那么我认为您需要使用代码创建一个新函数从你现有的

If you don't want to go adjusting all of your existing stored procedures that reference the function then I think you would need to create a new function with the code from your existing one

CREATE FUNCTION CalculateAverageForUser2
(
    @userid int,
    @param2 nvarchar(10) = NULL
)
RETURNS float
AS
/*Code from existing function goes here*/

然后只需将现有函数更改为以下内容

Then just change the existing function to the following

ALTER FUNCTION CalculateAverageForUser 
(
 @userid int
)
RETURNS float
AS
BEGIN
RETURN dbo.CalculateAverageForUser2(@userid, DEFAULT)
END

这篇关于SQL Server 中的可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！