?
Solved

SQL UDF, return years and months

Posted on 2009-12-21
10
Medium Priority
?
427 Views
Last Modified: 2012-05-08
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. :)
0
Comment
Question by:SnowThief
  • 6
  • 3
10 Comments
 
LVL 13

Accepted Solution

by:
sameer2010 earned 400 total points
ID: 26096743
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
 
LVL 43

Expert Comment

by:pcelba
ID: 26096811
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
 

Author Closing Comment

by:SnowThief
ID: 31668516
A++++++, works exactly as requested, fast delivery, no problems, would ask this expert again. :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:SnowThief
ID: 26096836
Now I feel bad. Well, thanks for your help anyway, pcelba, I really appreciate it.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26096852
OK, no problem :-)
0
 

Author Comment

by:SnowThief
ID: 26096873
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
 

Author Comment

by:SnowThief
ID: 26097052
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
 

Author Comment

by:SnowThief
ID: 26097100
Minor type in the above, "IF (@MonthFrom = 12)" should of course be "IF (@MonthFrom = 13)".
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26097217
SQL functions do not recognize parameters passed by reference, so passed variables are not updated. Your code should work as you need.
0
 

Author Comment

by:SnowThief
ID: 26097253
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question