SQL函数作为默认参数值? [英] SQL function as default parameter value?

问题描述

我尝试使用此更改默认参数值:

I tried changing a default parameter value with this:

ALTER PROCEDURE [dbo].[my_sp]
@currentDate datetime = GETDATE()

所有 SQL 预编译器给我的都是这个错误:

and all the SQL pre-compiler gave me was this error:

消息 102，级别 15，状态 1，过程 my_sp，第 8 行 '(' 附近的语法不正确.

Msg 102, Level 15, State 1, Procedure my_sp, Line 8 Incorrect syntax near '('.

我已经创建了程序.(我不确定这是否相关.)我使用的是空默认值并稍后检查，但这似乎不正确.我可以在一行中完成吗?

I have already created the procedure. (I'm not sure if that's relevant.) I was using a null default value and checking for that later, but that doesn't seem proper. Can I do this in one line?

<小时>更新:我要离开 MSDN 的存储过程参数说明:

[ = default ] 是参数的默认值.如果定义了默认值，则无需为该参数指定值即可执行该函数.

[ = default ] Is a default value for the parameter. If a default value is defined, the function can be executed without specifying a value for that parameter.

注意:
可以为 CLR 函数指定默认参数值，但 varchar(max) 和 varbinary(max) 数据类型除外.

Note:
Default parameter values can be specified for CLR functions except for the varchar(max) and varbinary(max) data types.

当函数的参数有默认值时，调用函数取默认值时必须指定关键字DEFAULT.这种行为不同于在存储过程中使用带有默认值的参数，在存储过程中省略参数也意味着默认值.

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.

我读错了吗?

非常感谢.

推荐答案

存储过程参数的默认值必须是常量.您需要执行以下操作...

Default value for stored procedures parameter have to be constants. You'd need to do the following...

ALTER Procedure [dbo].[my_sp]
@currentDate datetime = null
AS
IF @currentDate is null
SET @currentDate = getdate()

这篇关于SQL函数作为默认参数值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！