問題描述

考慮以下存儲過程:

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

調(diào)用時，參數(shù)聲明失敗并顯示錯誤將數(shù)據(jù)類型 nvarchar 轉(zhuǎn)換為日期時出錯".這可以通過如下更改代碼來解決:

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

但是，假設(shè) @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() 作為形式參數(shù)傳遞如下:

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

EXEC MySchema.MyProcedure GETDATE()

因為這也會產(chǎn)生可怕的將數(shù)據(jù)類型 nvarchar 轉(zhuǎn)換為日期時出錯"的錯誤.唯一的解決方法是先聲明一個變量，然后傳遞該變量:

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;

這是為什么?源數(shù)據(jù)類型和目標數(shù)據(jù)類型都是 DATETIME.理論上，將GETDATE()的結(jié)果作為參數(shù)的默認值或形參的值，都不會發(fā)生數(shù)據(jù)類型轉(zhuǎn)換錯誤.

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.

是否有一些技術(shù)原因?qū)е逻@不起作用?MSDN 文檔中沒有任何內(nèi)容表明它不應(yīng)該工作.

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 子標題下>參數(shù)部分:

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

參數(shù)的默認值.如果為一個定義了默認值參數(shù)，程序可以在不指定值的情況下執(zhí)行對于那個參數(shù).默認值必須是常量，也可以是NULL. 常量值可以是通配符的形式，使得它將參數(shù)傳遞給參數(shù)時可以使用 LIKE 關(guān)鍵字程序.

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.

強調(diào)我的.

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;
    ...

這篇關(guān)于為什么不能將 GETDATE() 用作過程參數(shù)的默認值或 EXECUTE 語句中的值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！