What is the correct way to use a WHILE loop in SQL?

Question

SQL

Good afternoon. The problem is the following.
It is necessary to determine:
1) The serial number of the day in the calendar year
2) The name of the day
3) The serial number of the week in the calendar month
4) The serial number of the month in the calendar year
5) The serial number of the quarter in the calendar year
For several years. For example, from 1600-1605
I did this:

DECLARE @Year VARCHAR (50)
DECLARE @EndYear VARCHAR (50)
SET @Year = '02/02/2002'
SET @EndYear = '01/31/2014'
SET LANGUAGE Russian
while (@Year <= @EndYear)
BEGIN
SELECT CAST(convert(char(8),@Year,112) as datetime)							        AS 'CalendarYear',
     DATENAME(month, @Year)											            AS 'Month Name', 
     DATEPART(DAYOFYEAR,@Year)											        AS 'NumberDayInCalendarYear',
     DATEPART(QUARTER, @Year)												        AS 'NumverOfQuarter',
     DATEPART(MONTH, @Year)													    AS 'NubmerMounthInCalendarYear',
     DATEPART(WEEK, @Year)														AS 'NumberOfWeekInCalendarYear',
     CAST(convert(char(6),@Year,112)+'01' as datetime)							AS 'CalendarMonthStartDate',
     DATEADD(day,-1, CONVERT(char(6), DATEADD(MONTH,1,@Year),112)+'01')			AS 'CalendarMonthEndDate'
     
END

However, this results in an infinite loop. How to do it right through the loop?
Thanks in advance!

Answer 1

I would do it better without a loop:
* I would make a table with years as a hierarchical query.
In oracle, it's like this:

select level from dual
connect by level < 2000

* I would add this table to your request
Plus - speed, because there are no procedures.

Answer 2

To prevent the loop from being infinite, insert something like this into the body of the loop (before end)
, in general, the meaning is that the variable that you check in the while condition (@Year <= @EndYear) has an increment with each iteration of the loop.