时间戳

首页 > 解决方案 > 使用游标遍历 SQL Server 中的表变量

sql - 使用游标遍历 SQL Server 中的表变量

问题描述

我有一个存储过程的参数，它以格式获取一些数据

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

标签： sqlsql-serversql-server-2008

解决方案

这使用两部分 - 首先将您的字符串转换为表格，然后将批量插入到您的目的地。不需要游标。

** 请原谅任何语法错误，因为我在没有访问您的实际表或函数的情况下执行此操作，因此无法对其进行测试，但您明白了

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

推荐阅读