SQL 存储过程参数默认值 - 这是常量还是变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14137925/
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
Stored Procedure parameter default value - is this a constant or a variable
提问by whytheq
Here is my code:
这是我的代码：
USE [xxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[problemParam]
@StartDate INT = CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))),
@EndDate INT = NULL
AS
BEGIN
SSMSis not too happy with the defaultvalue I've used - in the MSDN DEFINITION HEREit says that the default value needs to be a constant rather than a variable.
SSMS对我使用的默认值不太满意- 在MSDN DEFINITION HERE 中它说默认值需要是一个常量而不是一个变量。
Is CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112)))a variable or a constant? It's not a variable in the traditional way I think of a variable but then again it's not a constant like '03 jan 2013'is.
是CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112)))变量还是常数？它不是我认为变量的传统方式中的变量，但它又不是像'03 jan 2013'现在这样的常量。
How do I get around this? Move CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112)))to the client that is calling the stored procedure?
我该如何解决这个问题？移动CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112)))到调用存储过程的客户端？
EDIT
编辑
Possible duplicate as I've just spotted this SO POST
可能重复，因为我刚刚发现了这个SO POST
回答by Damien_The_Unbeliever
It has to be a constant- the value has to be computable at the time that the procedure is created, and that one computation has to provide the value that will always be used.
它必须是一个常量- 在创建过程时该值必须是可计算的，并且一次计算必须提供将始终使用的值。
Look at the definition of sys.all_parameters:
看下定义sys.all_parameters：
default_valuesql_variantIfhas_default_valueis 1, the value of this column is the value of the default for the parameter; otherwise,NULL.
default_valuesql_variant如果has_default_value为 1，则该列的值为该参数的默认值；否则，NULL。
That is, whatever the default for a parameter is, it has to fit in that column.
也就是说，无论参数的默认值是什么，它都必须适合该列。
As Alex K pointed out in the comments, you can just do:
正如亚历克斯 K 在评论中指出的那样，你可以这样做：
CREATE PROCEDURE [dbo].[problemParam]
@StartDate INT = NULL,
@EndDate INT = NULL
AS
BEGIN
SET @StartDate = COALESCE(@StartDate,CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))))
provided that NULLisn't intended to be a valid value for @StartDate.
前提是NULL它不是 的有效值@StartDate。
As to the blog post you linked to in the comments - that's talking about a very specific context - that, the resultof evaluating GETDATE()within the context of a singlequery is often considered to be constant. I don't know of many people (unlike the blog author) who would consider a separate expression inside a UDF to be part of the same query as the query that callsthe UDF.
至于您在评论中链接到的博客文章 - 这是在谈论一个非常具体的上下文 -在单个查询的上下文中评估的结果通常被认为是恒定的。我不知道有多少人（与博客作者不同）会将 UDF 中的单独表达式视为与调用UDF的查询相同的查询的一部分。GETDATE()
