Year List

[ter79]

I'm sure this has been asked, but I can't find anything on it.

I need to create a stored procedure that will create a list of years from 2004 to 2010, so I can incorporate it in a SQL Reporting Services Report.


Thanks in advance

 

[vongrunt]

Simple:

create procedure blah(@from smallint, @to smallint)
as
	declare @t table(yr smallint)
	while @from <=@to
	begin
		insert into @t values(@from)
		set @from = @from + 1
	end

	select yr from @t order by yr

go

exec blah 2004, 2010

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JayKusch]

DECLARE @YearList Char(34)

SELECT @YearList = CONVERT(Char(4),DatePart(yy,DateAdd(yy,-1,GetDate()))) + ',' +
CONVERT(Char(4),DatePart(yy,DateAdd(yy,0,GetDate()))) + ',' +
CONVERT(Char(4),DatePart(yy,DateAdd(yy,1,GetDate()))) + ',' +
CONVERT(Char(4),DatePart(yy,DateAdd(yy,2,GetDate()))) + ',' +
CONVERT(Char(4),DatePart(yy,DateAdd(yy,3,GetDate()))) + ',' +
CONVERT(Char(4),DatePart(yy,DateAdd(yy,4,GetDate()))) + ',' +
CONVERT(Char(4),DatePart(yy,DateAdd(yy,5,GetDate())))
print @YearList

Thanks

J. Kusch