問題描述

考慮以下存儲過程:

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

調用時，參數(shù)聲明失敗并顯示錯誤將數(shù)據(jù)類型 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() 作為形式參數(shù)傳遞如下:

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

EXEC MySchema.MyProcedure GETDATE()

因為這也會產生可怕的將數(shù)據(jù)類型 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;

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

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 子標題下>參數(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 關鍵字程序.

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

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