Stored Procedure parameter default value - is this a constant or a variable
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
Ifhas_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.
whytheq
Current addictions: DAX / POWERSHELL Time served with: (T-)sql / MDX / VBA / SSRS Would like more time for the following: C# Python Maxim: if you build something idiot-proof, the world will build a better idiot
Updated on February 10, 2020Comments
-
whytheq about 4 years
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