if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTable
go
/*
  功能：連續(xù)字符串（數(shù)字或日期）以table形式返回
  作者：zhang502219048 2018-12-10
  腳本來(lái)源：https://www.cnblogs.com/zhang502219048/p/11108991.html 
-- 示例1（數(shù)字）：
  select * from dbo.fun_ConcatStringsToTable(1, 10000)
-- 示例2（數(shù)字文本）：
  select * from dbo.fun_ConcatStringsToTable('1', '10000')
-- 示例3（日期）：
  declare @dateBegin datetime = '2009-1-1', @dateEnd datetime = '2018-12-31'
  select * from dbo.fun_ConcatStringsToTable(@dateBegin, @dateEnd)
-- 示例4（日期文本）：
  select * from dbo.fun_ConcatStringsToTable('2009-1-1', '2018-12-31')
**/
create function [dbo].[fun_ConcatStringsToTable]
(
  @strBegin as nvarchar(100),
  @strEnd as nvarchar(100)
)
returns @tempResult table (vid nvarchar(100))
as
begin
  --數(shù)字
  if isnumeric(@strBegin) = 1 and isnumeric(@strEnd) = 1
  begin
    --使用CTE遞歸批量插入數(shù)字?jǐn)?shù)據(jù)
    ;with cte_table(id) as
    (
      select cast(@strBegin as int)
      union all
      select id + 1
      from cte_table
      where id < @strEnd
    )
    insert into @tempResult
    select cast(id as nvarchar(100))
    from cte_table 
    option (maxrecursion 0)
  end
  --日期
  else if isdate(@strBegin) = 1 and isdate(@strEnd) = 1
  begin
    --使用CTE遞歸批量插入日期數(shù)據(jù)
    ;with cte_table(CreatedDate) as
    (
      select cast(@strBegin as datetime)
      union all
      select dateadd(day, 1, CreatedDate)
      from cte_table
      where CreatedDate < @strEnd
    )
    insert into @tempResult
    select convert(varchar(10), CreatedDate, 120)
    from cte_table
    option (maxrecursion 0)
  end

  return;
end
go

-- 示例1（數(shù)字）：
  select * from dbo.fun_ConcatStringsToTable(1, 10000)
-- 示例2（數(shù)字文本）：
  select * from dbo.fun_ConcatStringsToTable('1', '10000')
-- 示例3（日期）：
  declare @dateBegin datetime = '2009-1-1', @dateEnd datetime = '2018-12-31'
  select * from dbo.fun_ConcatStringsToTable(@dateBegin, @dateEnd)
-- 示例4（日期文本）：
  select * from dbo.fun_ConcatStringsToTable('2009-1-1', '2018-12-31')

with cte_table(CreatedDate) as
(
  select cast('2017-12-1' as datetime)
  union all
  select dateadd(month, 1, CreatedDate)
  from cte_table
  where CreatedDate < '2018-04-01'
)
select convert(varchar(7), CreatedDate, 120) as YearMonth
from cte_table
option (maxrecursion 0)