存储过程参数默认值 - 这是常量还是变量 [英] Stored Procedure parameter default value - is this a constant or a variable

问题描述

这是我的代码:

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 对我使用的 默认 值不太满意 - 在 MSDN DEFINITION HERE 它说默认值需要是一个常数而不是一个变量.

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.

CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))) 是变量还是常量?它不是我认为变量的传统方式中的变量，但它又不是像 '03 jan 2013' 那样的常量.

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))) 移动到调用存储过程的客户端?

How do I get around this? Move CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))) to the client that is calling the stored procedure?

编辑

可能重复，因为我刚刚发现了这个SO POST

Possible duplicate as I've just spotted this SO POST

推荐答案

它必须是一个常量 - 该值必须在创建过程时是可计算的，并且一个计算必须提供将始终使用的值.

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.

看sys.all_parameters<的定义/a>:

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.

正如 Alex K 在评论中指出的那样，你可以这样做:

As Alex K pointed out in the comments, you can just do:

CREATE PROCEDURE [dbo].[problemParam] 
    @StartDate INT = NULL,
    @EndDate INT = NULL
AS  
BEGIN
   SET @StartDate = COALESCE(@StartDate,CONVERT(INT,(CONVERT(CHAR(8),GETDATE()-130,112))))

前提是 NULL 不是 @StartDate 的有效值.

provided that NULL isn't intended to be a valid value for @StartDate.

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

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.

这篇关于存储过程参数默认值 - 这是常量还是变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！