Adding a default value to optional parameter of stored proce

Question

I want to have a default value for my SQL Server 2008 stored proc as below:

Create Process sp_Transactions1
     @earliestDate varchar(12)= CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4))  + RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-2, getdate())) AS varchar(2)), 2)+ '01'
As

But it won't compile

Is this not possible?

possible duplicate of Stored Procedure parameter default value - is this a constant or a variable — Kritner
– Kritner, Commented Dec 19, 2014 at 13:39

Razvan Socol · Accepted Answer · 2014-12-28 11:42:36Z

1

As Kritner said above, the default value of parameters can be only a constant. However, if the NULL value does not have another meaning in this context, you can use something like this:

CREATE PROCEDURE sp_Transactions1
     @earliestDate date=NULL
AS
IF @earliestDate IS NULL 
    SET @earliestDate=CONVERT(DATE,
        STR(YEAR(DATEADD(m,-3, getdate())),4)
        +RIGHT('0'+CONVERT(VARCHAR(2),MONTH(DATEADD(m,-2, getdate()))),2)
        +'01'
    )
--...

answered Dec 28, 2014 at 11:42

Razvan Socol

5,7592 gold badges22 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Adding a default value to optional parameter of stored proce

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related