SQL: Is there a way to iteratively DECLARE a varia

Question

Is there a way to accomplish something like this in SQL:

DECLARE @iter = 1

WHILE @iter<11
BEGIN
DECLARE @('newdate'+@iter) DATE = [some expression that generates a value]
SET @iter = @iter + 1
END

At the end I would have 10 variables:

@newdate1
@newdate2
@newdate3
@newdate4
@newdate5
@newdate6
@newdate7
@newdate8
@newdate9
@newdate10

Update:

Based on a comment, I think I should specify why I want to do this. I am working with Report Builder 3.0. I am going to make a report where the input will be a start date and an end date (in addition to one other parameter). This will generate data between the date range. However, the user also wants to check the same date range for all other years in the set 2013 -> current year.

The tricky part is this: the user can enter a date range in any year between 2013 and the current year and I need to return data for the input year and also data for the other years. For example, if the user enters in 1/1/2014 - 6/1/2014 then I need to return the same range but for the years 2013, 2015, and 2016.

Example input:

1/1/2016 - 6/1/2016

Report must generate data for these values:

1/1/2013 - 6/1/2013
1/1/2014 - 6/1/2014
1/1/2015 - 6/1/2015
1/1/2016 - 6/1/2016

If there is a better way to do this, I'm all ears.

Answer 1

Other methods mentioned are much better for what you are trying to do, but in terms of answering the question "Is there a way to iteratively DECLARE a variable?", you could do something like what I have below using dynamic SQL. Basically you would create a string, using a loop, that contains your declare statements. Then you would create an additional string (or strings) to use them. In this example, I'm simply creating the variables and setting them to today's date. Then I select each variable.

DECLARE @iter INT = 1, @SQL VARCHAR(MAX) = '', @MoreSQL VARCHAR(MAX) = '';

WHILE @iter < 11
BEGIN
    SET @SQL += 'DECLARE @NewDate' + CAST(@iter AS VARCHAR(2)) + ' DATE = GETDATE() '

    SET @iter += 1
END

SET @iter = 1

WHILE @iter < 11
BEGIN
    SET @MoreSQL  += 'SELECT @NewDate' + CAST(@iter AS VARCHAR(2)) + ' '

    SET @iter += 1
END

SET @SQL += @MoreSQL

EXEC (@SQL)