Assign a variable to a Dynamic Data?

Question

Assign a variable to a Dynamic Data?

Navigation

#1 by (1 votes)

2

My goal is to create a procedure in SQL Server , which has by default two variables, one with the start date of (hoje - 3 anos) and another as the end date equal to hoje . I already use for other operations (for example a between) this format to get the date.

I tried to do this:

    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 I run

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

What's wrong?

sql sql-server tsql stored-procedures

asked by anonymous 23.05.2017 / 20:22

1 answer

Error performing findviewbyid HttpClient - WebException: The underlying connection was closed: The connection was unexpectedly closed

score 1 · Accepted Answer

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

See how you could get this code so that if you pass some value, it will be used, otherwise, GetDate will be invoked: See also that it was not necessary to remove '-' 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

In addition, I used other functions:

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

DateAdd - > Used to do the subtraction of 3 years;

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