34

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.

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.

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

EDIT

Possible duplicate as I've just spotted this SO POST

Improve this question
2
  • 1
    The result of GETDATE() varies by time so it cannot be constant. Use NULL/a magic value for a default, detect it & assign your expression should it match. Commented Jan 3, 2013 at 11:24
  • @AlexK. I'm not sure if you're correct: surely GETDATE() is evaluated when it is called and then constant. See these blog posts - sees to be debatable Commented Jan 3, 2013 at 12:15

1 Answer 1

Reset to default
42

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:

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.

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:

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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

+1 for this solution. It's definitely a subtle difference if I change has to be computable at the time that the procedure is created, to has to be computable at the time that the procedure is executed, then as far as the sproc is concerned GETTIME() would be a constant. Like the use of COALESCE I used IF @StartDate IS NULL ...
@whytheq - if it only had to be computable at the time that the procedure was executed, what wouldn't be allowed at that time?
...if it takes 2mins to execute and I set @StartDate to a traditional variable say @StartDate INT = @x then initially set @x to '01 jan 2013' and then later in the proc change it to '02 jan 2013' then I'd say that is undoubtedly a variable and not a constant; splitting hairs a bit.
Actually, has_default value is not working at all. There is a connect about this posted on 2006 and the issue is still not fixed - connect.microsoft.com/SQLServer/feedback/details/234143/…
@VenkataramanR - COALESCE is "return the first non-null parameter from this list". @StartDate is the first parameter here. If it's non-null, it's the equivalent of a harmless @StartDate = @StartDate assignment.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.