Original SQL table-valued function: Gets the number of months from the current month calculated from the forward custom

Today I find that it is not too long to read a blog post, as long as you can describe what you want to express, to let people know what you want to say. Because I read some long blog today, I really feel that there are too many knowledge points, it will make people swallowed.

This blog post is similar to the one I published yesterday to address a statistical requirement, and the result is a request to return the month from which the definition input was extrapolated from the current month

Like to summarize me, for this write a table-valued function to solve this requirement. Now it is recorded and I hope to help some friends who have encountered such problems.

First we look at the execution:

Returns data for the last year that is 12 months, executing the calling function: SELECT * FROM [Fn_runmonth] (-12)

The specific functions are as follows:

-- =============================================
--Call: SELECT * FROM [Fn_runmonth] (-12)
--Create date:2015-01-12
--Description: Gets @num months from the current month and returns to table
-- =============================================
CREATE FUNCTION [dbo]. [Fn_runmonth] (@num int=-12)
RETURNS @tab TABLE
(
Sale_year VARCHAR (10),
Xsdate VARCHAR (10),
Sort INT
)
As
BEGIN

DECLARE @i INT
SET @i=0
while (@i> @num)
BEGIN
INSERT into @tab
SELECT [Sale_year] = year (DATEADD (month, @i, GETDATE ())),
[Xsdate] = month (DATEADD (month, @i, GETDATE ())),
Sort = @i
SET @[email protected]
END
RETURN
END

if friends think that may be used later, welcome reprint, of course, can also recommend, thank you.

PS: This blog post is written using Windows Live Writer 2012, and the format effect may not be good.

Original SQL table-valued function: Gets the number of months from the current month calculated from the forward custom