if object_id('dbo.fun_ConcatStringsToTable') is not null drop function dbo.fun_ConcatStringsToTable
go
/*
功能:连续字符串(数字或日期)以table形式返回
作者:zhang502219048 2018-12-10
脚本来源:https://www.cnblogs.com/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)