Here is the reference material for
CREATE FUNCTION[
^]
The first thing you need to do is move your variables @StartDate and @EndDate out of this SQL to become parameters to the function ... e.g.
CREATE FUNCTION [dbo].[fnYourFunction]
(
@StartDate DATETIME,
@EndDate DATETIME
)
Then you need to define the values you want to return ... in this case you're trying to return the results from
SELECT
CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth1
so your output from the function can be defined by
RETURNS @output TABLE(yearmonth VARCHAR(6)
Next include your SQL into the body of the function by surrounding it by
BEGIN ... END
and including an
RETURN
statement.
Finally, because you have defined the output table, you just need to add the results from the query into the table e.g.
INSERT INTO @output
So the whole thing becomes
CREATE FUNCTION [dbo].[fnYourFunction]
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS @output TABLE(yearmonth VARCHAR(6)
)
BEGIN
With DT_TempTbl as
(
SELECT @StartDate AS YearMonth
UNION ALL
SELECT DATEADD(MONTH, 1, YearMonth)
FROM DT_TempTbl
WHERE YearMonth <= @EndDate
)
INSERT INTO @output
SELECT CONVERT(VARCHAR(6), YearMonth, 112) AS YearMonth1
FROM DT_TempTbl
ORDER BY YearMonth1
RETURN
END
I called it using your test data as follows
select * from dbo.fnYourFunction(CAST('2010-01-01' as DATETIME), getdate())