SQL UDF, return years and months

CREATE FUNCTION dbo.YearsAndMonths(@YearFrom SMALLINT, @MonthFrom SMALLINT, @YearTo SMALLINT, @MonthTo SMALLINT)
RETURNS @t TABLE

How do I finish this? dbo.YearsAndMonths(2008, 7, 2010, 4) should return:

Year Month
2008 7
2008 8
2008 9
2008 10
2008 11
2008 12
2009 1
2009 2
2009 3
2009 4
2009 5
2009 6
2009 7
2009 8
2009 9
2009 10
2009 11
2009 12
2010 1
2010 2
2010 3
2010 4

I'm using SQL Server 2000. Thank you for your time. :)
SnowThiefAsked:
Who is Participating?
 
sameer2010Commented:
Use this:

CREATE FUNCTION dbo.YearsAndMonths(@YearFrom SMALLINT, @MonthFrom SMALLINT, @YearTo SMALLINT, @MonthTo SMALLINT)
RETURNS @t table (i smallint,j smallint)
as
begin

declare @rt table(y smallint, m smallint);
declare @yy smallint;
declare @mm smallint;
set @yy=@yearfrom;
set @mm=@monthfrom;

while((@yy<@yearto) or(@yy = @yearto and @mm<=@monthto))
begin
	insert into @rt values(@yy,@mm)
	if(@mm=12)
	begin
		set @mm=1
		set @yy=@yy+1
	end
	else
	begin
		set @mm=@mm+1
	end
end;
insert into @t 
select y,m
from @rt
return
end

Open in new window

0
 
pcelbaCommented:
I am not sure if SQL 2000 supports table functions... I am sure SQL 2000 does not support table variables.

I would recommend Stored procedure. Result can be stored to a temporary table if you don't like table returned by SP.

Call example:

EXEC dbo.YearsAndMonths 2008, 7, 2010, 4
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.YearsAndMonths 
	@YearFrom SMALLINT, @MonthFrom SMALLINT, @YearTo SMALLINT, @MonthTo SMALLINT
AS
BEGIN
  SET NOCOUNT ON;
  CREATE TABLE #Output (Year smallint, Month smallint)
  
  declare @Year int, @Month int
  set @Year = @YearFrom
  set @Month = @MonthFrom
  
  WHILE @Year * 100 + @Month <= @YearTo * 100 + @MonthTo
  BEGIN
    INSERT INTO #Output VALUES (@Year, @Month)
    set @Month = @Month + 1
    IF @Month = 13
    BEGIN
      set @Month = 1
      set @Year = @Year + 1
    END
  END
  
  SELECT Year, Month FROM #Output
  
END
GO

Open in new window

0
 
SnowThiefAuthor Commented:
A++++++, works exactly as requested, fast delivery, no problems, would ask this expert again. :)
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
SnowThiefAuthor Commented:
Now I feel bad. Well, thanks for your help anyway, pcelba, I really appreciate it.
0
 
pcelbaCommented:
OK, no problem :-)
0
 
SnowThiefAuthor Commented:
Oh, wait, can I ask a really quick question, Sameer or anyone else who knows? What's the purpose of @rt in the solution? Why not insert directly into @t? I changed the code to do that and it still seems to work just as well. I'm new to UDFs, though, so there's probably a reason to do it that way, I'd just like to know what it is. :)
0
 
SnowThiefAuthor Commented:
I got rid of all the temporary stuff, so my code now looks like this:

CREATE FUNCTION dbo.YearsAndMonths(@YearFrom SMALLINT, @MonthFrom SMALLINT, @YearTo SMALLINT, @MonthTo SMALLINT)
RETURNS @t TABLE (Year SMALLINT, Month SMALLINT) AS
BEGIN
      WHILE (@YearFrom <> @YearTo OR @MonthFrom <> @MonthTo)
      BEGIN
              INSERT INTO @t VALUES (@YearFrom, @MonthFrom)
              SET @MonthFrom = @MonthFrom + 1
              IF (@MonthFrom = 12)
              BEGIN
                      SET @MonthFrom = 1
                      SET @YearFrom = @YearFrom + 1
              END
      END
      RETURN
END

Is there any danger in using the passed variables directly? If I pass a field to the function, can the function ever modify the values passed? I mean like passing a pointer in C or using ByRef in Visual Basic, so the function can change the values passed to it.
0
 
SnowThiefAuthor Commented:
Minor type in the above, "IF (@MonthFrom = 12)" should of course be "IF (@MonthFrom = 13)".
0
 
pcelbaCommented:
SQL functions do not recognize parameters passed by reference, so passed variables are not updated. Your code should work as you need.
0
 
SnowThiefAuthor Commented:
Thank you very much again. I can't give you any points, but I'll mod all your comments as helpful, in case that does anything. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.