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
This question is related to
sql
stored-procedures
sql-server-2008-r2
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_valuesql_variantIfhas_default_valueis 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.
Source: Stackoverflow.com