asked
Get year date while loop
I am trying to create a while loop counter to get year based on the @year and @id so for example if I chose year 2012 I will get 2012 and 2013 if @id =1 if @id =2 then I will get 2012,2013,2014. I did the Select year(getdate()) to get the current year but am stuck on creating the loop to insert the years into table variable based on the @year and @id. Any help would be appreciated.
I came up with the following.... This works well based on todays year declare @id int declare @year int DECLARE @GETYEAR TABLE ( YEAR INT ) SET @id = 0 SET @year = (SELECT YEAR(GETDATE())) WHILE @year <= (SELECT YEAR(GETDATE())+ @id) BEGIN SET @year = @year + 1 INSERT INTO @GETYEAR VALUES(@year) END INSERT INTO @GETYEAR(YEAR) SELECT YEAR(GETDATE()) SELECT * FROM @GETYEAR ORDER BY YEAR
declare @id int declare @year int DECLARE @GETYEAR TABLE ( YEAR INT ) SET @year = 2010 declare @CurrentYear int SET @id = 0 SET @CurrentYear = (SELECT YEAR(GETDATE())) INSERT INTO @GETYEAR VALUES(@year) WHILE @year <= (SELECT YEAR(GETDATE())+ @id) BEGIN SET @year = @year + 1 INSERT INTO @GETYEAR VALUES(@year) END SELECT * FROM @GETYEAR ORDER BY YEAR
@tombiernacki I would not recommend a Loop to acheive this. SQL Server is not build for such programming. Also, for your own good please always try to build a solution using a set based approach. Your problem can be easily done with a Recursive cte OR using cteTally numbers table (Arbitrary numbers table). Using cteTally numbers table this is how it can be done Declare @year INT, @id INT SELECT @year = 2012, @id = 2 ;WITH CTETally AS ( SELECT TOP(@id+1) N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n), (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n), (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n) ) SELECT @year + N - 1 FROM CTETally ORDER BY N Hope it helps.
13 People are following this question.
