为什么不能将 GETDATE() 用作过程参数的默认值或 EXECUTE 语句中的值?

2021-09-10 00:00:00 tsql sql-server

考虑以下存储过程:

CREATE OR ALTER PROCEDURE MySchema.MyProcedure
   @myDateTimeParam DATETIME = GETDATE()
AS
BEGIN
   -- Do something spectacular
END

调用时，参数声明失败并显示错误将数据类型 nvarchar 转换为日期时出错".这可以通过如下更改代码来解决:

Upon invocation, the parameter declaration fails with the error, "Error converting data type nvarchar to date." This can be worked around by changing the code as follows:

CREATE OR ALTER PROCEDURE MySchema.MyProcedure
   @myDateTimeParam DATETIME = NULL
AS
BEGIN

    IF @myDateTimeParam IS NULL
        SET @myDateTimeParam = GETDATE();

   -- Do something spectacular
END

但是，假设 @myDateTimeParam 没有默认值:

However, assume that @myDateTimeParam did not have a default value:

CREATE OR ALTER PROCEDURE MySchema.MyProcedure
   @myDateTimeParam DATETIME
AS
BEGIN
   -- Do something spectacular
END

在这种情况下，您不能简单地将 GETDATE() 作为形式参数传递如下:

In this case, you cannot simply pass GETDATE() as a formal parameter as follows:

EXEC MySchema.MyProcedure GETDATE()

因为这也会产生可怕的将数据类型 nvarchar 转换为日期时出错"的错误.唯一的解决方法是先声明一个变量，然后传递该变量:

as this also produces the dreaded "Error converting data type nvarchar to date" error. The only workaround to this is to first declare a variable and then pass the variable:

DECLARE @myDateTimeParam DATETIME = GETDATE();
EXEC MySchema.MyProcedure @myDateTimeParam;

这是为什么?源数据类型和目标数据类型都是 DATETIME.理论上，将GETDATE()的结果作为参数的默认值或形参的值，都不会发生数据类型转换错误.

Why is this? Both the source and target data types are DATETIME. In theory, a data type conversion error should not occur when using the result of GETDATE() as either the default value of a parameter or the value of a formal parameter.

是否有一些技术原因导致这不起作用?MSDN 文档中没有任何内容表明它不应该工作.

Is there some technical reason that this does not work? There's nothing in the MSDN documentation that indicates that it should not work.

推荐答案

这在文档中有所介绍，CREATE PROCEDURE (Transact-SQL)，在 default 子标题下>参数部分:

This is covered in the documentation, CREATE PROCEDURE (Transact-SQL), under the default subheading in the arguments section:

参数的默认值.如果为一个定义了默认值参数，程序可以在不指定值的情况下执行对于那个参数.默认值必须是常量，也可以是NULL. 常量值可以是通配符的形式，使得它将参数传递给参数时可以使用 LIKE 关键字程序.

A default value for a parameter. If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL. The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

强调我的.

GETDATE() 不是常量，因此不能用作 DEFAULT 值.因此，为什么您需要使用以下格式，因为 GETDATE() 的值是在运行时确定的:

GETDATE() is not a constant, so cannot be used an a DEFAULT value. Hence why you need to use the format below, as then the value of GETDATE() is determined at run time:

CREATE PROC YourProc @Param date = NULL
AS

    IF @Param IS NULL BEGIN
        SET @Param = GETDATE();
    END;
    ...

相关文章