sql server使用公用表表达式CTE通过递归方式编写通

if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTable
go
/
  功能连续字符串（数字或日期）以table形式返回
  作者zhang502219048 2018-12-10
  脚本来源https://.blogs./zhang502219048/p/11108991.html 
-- 示例1（数字）
  select  from dbo.fun_ConcatStringsToTable(1, 10000)
-- 示例2（数字文本）
  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
  --数字
  if isnumeric(@strBegin) = 1 and isnumeric(@strEnd) = 1
  begin
    --使用CTE递归批量插入数字数据
    ;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递归批量插入日期数据
    ;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（数字）
  select  from dbo.fun_ConcatStringsToTable(1, 10000)
-- 示例2（数字文本）
  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)