存储过程参数默认值 - 这是常量还是变量

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

SSMS is not too happy with the default value I've used - in the MSDN DEFINITION HERE it 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

1 个解决方案

#1

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_value sql_variant If has_default_value is 1, the value of this column is the value of the default for the parameter; otherwise, NULL.

default_value sql_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:

正如Alex 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 NULL isn'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 result of evaluating GETDATE() within the context of a single query 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 calls the UDF.

至于您在评论中链接到的博客文章 - 这是关于非常具体的上下文 - 在单个查询的上下文中评估GETDATE（）的结果通常被认为是不变的。我不知道有多少人（与博客作者不同）会认为UDF中的单独表达式是与调用UDF的查询相同的查询的一部分。

#1