Assign a variable a Dynamic Date?

Question

My goal is to create a SQL Server, which has by default two variables, one with the initial date of (hoje - 3 anos) and a final date equal to hoje. I already use for other operations (for example a between) this format to catch the date.

I tried to do it this way:

    Create procedure EXTRAÇÃO_SD1_SD2
-- Receber data formato 'AAAMMDD'
    @dataini VARCHAR(MAX) = REPLACE(CONVERT(char(10), GetDate()-1080,126),'-','')
    @datafim as varchar(10) = REPLACE(CONVERT(char(10), GetDate(),126),'-','')
As 
Begin 
-- continua procedure

But I get the error below when running

Msg 102, Level 15, Status 1, Procedure EXTRAIO_SD1_SD2, Line 5 [Batch Start Line 0] Incorrect syntax near '('.

What’s wrong with it?

Answer 1

You used the wrong part of Procedure to make your assignments. Before the begin is the declaration of the identity of the Procedure, and inside, the body.
In the identity part you can define the input parameters, this if you want to use a reference date informed by the user, discarding for example the fixed use of GetDate.

See how this your code could look like so that if some value is passed, it will be used, otherwise the GetDate will be invoked:
Also note that it was not necessary to remove the '-' to perform the subtraction.

Create procedure EXTRAÇÃO_SD1_SD2 (@dataini varchar(10) = null, @datafim varchar(10) = null)
As 
Begin    
    --Para indicar a procedure que o usuário está passando datas no formato YYYYMMDD
    --Se não garantir isso, e estiver configurado como dmy vai dar erro na sua procedure
    set dateformat 'ymd'

    declare @dataI varchar(10) = convert(date, dateadd(year, -3, coalesce(@dataini, GetDate())))
    declare @dataF varchar(10) = convert(date, coalesce(@datafim, GETDATE()))

    ...
end

As a complement, I made use of other functions:

Set DateFormat -> It is necessary because if the server is with another graduate, the months can be exchanged for the days for example;

DateAdd -> Used to subtract 3 years;

Coalesce -> If no value is passed to the parameters, then it will fetch a Getdate value;