在SQL Server中使用游标循环访问表变量
在SQL Server中使用游标循环访问表变量
提问于 2019-03-28 02:46:29
我有一个存储过程的参数，它以下面的格式获取一些数据
1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,8/1/2018-9/1/2018,9/1/2018-10/1/2018,
10/1/2018-11/1/2018,11/1/2018-12/1/2018,12/1/2018-12/31/2018我有一个根据,字符拆分数据并将结果存储到表变量中的函数，如下所示：
declare @SPlitDates table(ItemNumber int, Item nvarchar(max))
insert into @SPlitDates
select *
from dbo.SPlitFunction(@RequestData, ',')在此之后，我必须对数据范围执行某些操作，因此我使用游标遍历临时表，如下所示
DECLARE cur CURSOR FOR
SELECT Item
FROM @SPlitDates
ORDER BY ItemNumber
OPEN cur
FETCH NEXT FROM cur INTO @monthStart
WHILE @@FETCH_STATUS = 0
BEGIN
-- Some operation
END我将在临时表中获得的最大数据点是12个月的日期范围。
我的问题是，除了游标之外，我是否可以使用其他东西来提高性能，或者当数据集真的这么小时，这并不重要。
谢谢
编辑-显示光标内部的操作
declare @SPlitDates table(ItemNumber int, Item nvarchar(max))
insert into @SPlitDates
select *
from dbo.SPlitFunction(@RequestData, ',')
declare @SPlitDatesData table (ItemNumber varchar(100), Item nvarchar(max))
declare @SPlitDatesAvgData table(Code nvarchar(100), Val decimal(18,2))
declare @dataFilter as nvarchar(max),
@SQL as nvarchar(max);
declare @monthStart nvarchar(100)
declare @count int
set @count = 0
--Declaring a cursor to loop through all the dates as defined in the requested quarter
DECLARE cur CURSOR FOR
SELECT Item
FROM @SPlitDates
ORDER BY ItemNumber
OPEN cur
FETCH NEXT FROM cur INTO @monthStart
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Period NVARCHAR(100)
SET @Period = @monthStart
INSERT INTO @SPlitDatesData
--split the dates to get the start and the end dates
SELECT *
FROM dbo.SPlitFunction(@Period, '-')
DECLARE @PeriodStart NVARCHAR(100)
DECLARE @PeriodEnd NVARCHAR(100)
SET @PeriodStart = (SELECT Item FROM @SPlitDatesData WHERE ItemNumber = 1)
SET @PeriodEnd = (SELECT Item FROM @SPlitDatesData WHERE ItemNumber = 2)
DELETE FROM @SPlitDatesData
--add the start and end dates to the filter
SET @dataFilter = 'StatusDate between convert(datetime,('''+@PeriodStart+'''))
and DATEADD(dy, 1, convert(datetime,('''+@PeriodEnd+''')))'
SET @count = @count +1;
SET @SQL = 'INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
VALUES (@count,
''SL Payroll'',(select dbo.GetAverageCycleBetweenBids('''+@PeriodStart+''',
'''+@PeriodEnd+''',''SL''))
)'
EXEC SP_ExecuteSQL @SQL, N'@count int', @count;
SET @count = @count +1;
SET @SQL = 'INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
VALUES (@count,
''GV Payroll'',(select dbo.GetAverageCycleBetweenBids('''+@PeriodStart+''',
'''+@PeriodEnd+''',''GV''))
)'
EXEC SP_ExecuteSQL @SQL , N'@count int', @count;
SET @count = @count +1;
SET @SQL = 'Insert into #BidAverageCycleCalculation (SortOrder,Code,Data)
Values (@count,
''Global Payroll'',(select dbo.GetAverageCycleBetweenBids('''+@PeriodStart+''',
'''+@PeriodEnd+''',''GVS''))
)'
EXEC SP_ExecuteSQL @SQL, N'@count int', @count;
SET @count = @count +1;
SET @SQL = 'Insert into #BidAverageCycleCalculation (SortOrder,Code,Data)
Values (@count,
''TimeHCM'',(select dbo.GetAverageCycleBetweenBids('''+@PeriodStart+''',
'''+@PeriodEnd+''',''Time''))
)'
EXEC SP_ExecuteSQL @SQL, N'@count int', @count;
delete from @SPlitDatesAVgData
FETCH NEXT FROM cur INTO @monthStart
END
CLOSE cur
DEALLOCATE cur回答 1
Stack Overflow用户
回答已采纳
发布于 2019-03-28 13:27:22
这使用两个部分-首先将字符串转换为表，然后对目标进行批量插入。不需要游标。
**请原谅任何语法错误，因为我没有访问您的实际表或函数，所以无法对其进行测试，但您明白我的想法
declare @in varchar(max)
set @in= '1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,8/1/2018-9/1/2018,9/1/2018-10/1/2018,10/1/2018-11/1/2018,11/1/2018-12/1/2018,12/1/2018-12/31/2018'
declare @xml xml;
set @xml= convert(xml,'<r><f>'+replace(replace(@in,',','</t></r><r><f>'),'-','</f><t>') +'</t></r>')
declare @t table(id int identity, f date, t date)
insert @t
select
Tbl.Col.value('f[1]', 'date') f,
Tbl.Col.value('t[1]', 'date') t
FROM @xml.nodes('//r') Tbl(Col)
select * from @t
declare @count int;
select @count=count(*) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id, 'SL Payroll',(select dbo.GetAverageCycleBetweenBids(f,t,'SL')) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id+@count,'GV Payroll',(select dbo.GetAverageCycleBetweenBids(f,t,'GV')) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id+@count*2,'Global Payroll',(select dbo.GetAverageCycleBetweenBids(f,t,'GVS')) from @t
INSERT INTO #BidAverageCycleCalculation (SortOrder, Code, Data)
select id+@count*3,'TimeHCM',(select dbo.GetAverageCycleBetweenBids(f,t,'Time')) from @t页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55384483
复制相关文章
相似问题
腾讯云开发者
