SQL Server - 将默认日期时间值分配给存储过程中的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6413277/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - Assign default DateTime value to parameters in Stored Procedure
提问by Sunday Ironfoot
Maybe I'm being an idiot here, but I'm trying to assign a default DateTime value to a Stored Procedure in MS SQL Server 2005 but can't get it to work eg:
也许我在这里是个白痴，但我试图为 MS SQL Server 2005 中的存储过程分配一个默认的 DateTime 值，但无法让它工作，例如：
CREATE PROCEDURE MyProcedure
(
@MyInteger INT = 123,
@MyDateTime DATETIME = CONVERT(DATETIME, '1753-01-01 00:00:00', 20)
)
AS
BEGIN ... (snip)
So if the @MyDateTimeinput parameter isn't specified, it should use '1753-01-01 00:00:00' by default, but I get the error...
因此，如果@MyDateTime未指定输入参数，则默认情况下应使用“1753-01-01 00:00:00”，但我收到错误...
Incorrect syntax near the keyword 'CONVERT'.
I can get it to work for Integers just fine. What am I doing wrong?
我可以让它为整数工作就好了。我究竟做错了什么？
Edit:from the answers below, I eventually went with the following inside the Sproc itself:
编辑：从下面的答案中，我最终在 Sproc 内部使用了以下内容：
CREATE PROCEDURE MyProcedure
(
@MyInteger INT = 123,
@MyDateTime DATETIME = NULL
)
AS
BEGIN
SET NOCOUNT ON
SET @MyDateTime = COALESCE(@MyDateTime, CONVERT(DATETIME, '1753-01-01 00:00:00', 20))
...snip...
END
采纳答案by JonH
@MyDateTime DATETIME ='1753-01-01 00:00:00'
@MyDateTime DATETIME ='1753-01-01 00:00:00'
Or right within the sproc you can do this:
或者就在 sproc 中，您可以执行以下操作：
SELECT @MyDateTime = (SELECT CONVERT(DATETIME, '1753-01-01 00:00:00', 20))
SELECT @MyDateTime = (SELECT CONVERT(DATETIME, '1753-01-01 00:00:00', 20))
The error is due to calling CONVERT, which you cannot do when defaulting parameters.
该错误是由于调用了 CONVERT，而在默认参数时无法执行此操作。
回答by Ben Robinson
You can't have the CONVERT() function call in there, just assign the default value using the relevent datetime string.
您不能在那里调用 CONVERT() 函数，只需使用相关日期时间字符串分配默认值即可。
