SQL 将参数的默认值传递给表值函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15690464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing default values of parameters to Table Valued Functions
提问by wootscootinboogie
create function Xtest
(@d1 varchar(3)=null
,@d2 varchar(3)=null)
returns table
as
return
with code_list(code_pattern)
as
(
select x.code_pattern
from (values (@d1),(@d2)) as x(code_pattern)
where x.code_pattern is not null
),y
as
(
select substring(code,1,3) as code
from tblicd
where substring(code,1,3) in
(
select * from code_list
)
)
select * from y
is my function which will make sense when it's fully finished. If I try to run this query and I don't supply two parameters, it will fail. I have the same code as a stored procedure and if I only enter one parameter, it works fine and assigns the second parameter null. Is there a way that I can pass nullas a parameter value if the parameter isn't supplied, like one can do with stored procedures?
是我的功能，当它完全完成时才有意义。如果我尝试运行此查询并且我不提供两个参数，它将失败。我有与存储过程相同的代码，如果我只输入一个参数，它工作正常并将第二个参数分配为空。null如果没有提供参数，有没有一种方法可以作为参数值传递，就像使用存储过程一样？
The function does compile.
该函数确实可以编译。
回答by Aaron Bertrand
You can't omit the parameters when you call a function. This isn't anything you're doing wrong in the syntax, it's just simply not supported by SQL Server. Stored procedures have optional parameters, but functions do not.
调用函数时不能省略参数。这不是您在语法上做错的任何事情，只是 SQL Server 不支持它。存储过程有可选参数，但函数没有。
You can, however, supply default:
但是，您可以提供default：
SELECT code FROM dbo.Xtest(default, default);
Or in this case if you know the defaults are NULLyou can do:
或者在这种情况下，如果您知道默认值，则NULL可以执行以下操作：
SELECT code FROM dbo.Xtest(NULL, NULL);
Also please always use the schema prefix(in this case dbo.) when creating and referencing any object, especiallyfunctions.
此外，在创建和引用任何对象（尤其是函数）时，请始终使用架构前缀（在本例中dbo.）。
