SQL函数作为默认参数值?

I tried changing a default parameter value with this:

我尝试用以下方法更改默认参数值:

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

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

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

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

Msg 102，第15级，状态1，过程my_sp，第8行错误语法在'('。

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?

我已经创建了这个过程。(我不确定这是否相关。)我使用的是空默认值，稍后再检查，但这似乎不合适。我可以用一行来做吗?

Update: I was going off of MSDN's description of stored procedure parameters:

[ = 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.

[= default]是参数的默认值。如果定义了默认值，则可以在不指定该参数的值的情况下执行该函数。

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

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

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，以检索默认值。这种行为与在存储过程中使用带有默认值的参数不同，在存储过程中省略参数也意味着默认值。

Am I reading this wrong?

我读错了吗?

Many thanks.

多谢。

6 个解决方案

#1

131

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()

#2

I don't think that is possible, you have to use a literal (constant) value as the default.

我认为这是不可能的，你必须使用文字(常量)值作为默认值。

However you can do this:

但是你可以这样做:

Set @currentDate = Coalesce(@currentDate , GetDate())

#3

You can try as follow:

你可以试试以下方法:

Set @CurrentDate=IsNull(@CurrentDate,GetDate())

#4

I infer you're using Microsoft SQL Server from the square brackets in your example.

我从示例中的方括号推断您正在使用Microsoft SQL Server。

From MSDN:

从MSDN:

Only a constant value, such as a character string; a scalar function (either a system, user-defined, or CLR function); or NULL can be used as a default.

只有常量值，如字符串;标量函数(系统、用户定义函数或CLR函数);或者NULL可以用作默认值。

The function GETDATE() returns a different value from time to time, so it is not a constant expression.

函数GETDATE()不时返回不同的值，因此它不是一个常量表达式。

#5

That value is not deterministic and cannot be used

这个值不是确定性的，不能使用

#6

Suggestion:

建议:

Set the default to NULL

将默认值设置为NULL。

Do the Default GETDATE() in the front end.

在前端执行默认的GETDATE()。

#1

131