SQL Server 表变量遍历方法

declare @PcrIdList table(PCRID bigint)
insert into @PcrIdList select PCRID from dbo.[PCR2_ExtendFields] group by PCRID
declare @PcrId bigint
while exists(select PCRID from @PcrIdList)
begin
    select @PcrId=PCRID from @PcrIdList
    print @PcrId
    delete from @PcrIdList where PCRID=@PcrId
end

 

分页方法：

--Method-1
select * from dbo.[PCR2_ExtendFields] order by [SID] offset 0 rows fetch next 10 rows only

--Method-2
select * from (
    select *, ROW_NUMBER() OVER(Order by [SID] ) AS RowId from dbo.[PCR2_ExtendFields]
) as b where RowId between 1 and 10

--Method-3
select top 10 * from dbo.[PCR2_ExtendFields] where [SID] not in (select top 0 [SID] from dbo.[PCR2_ExtendFields])

--Method-4
--CREATE procedure page_Demo
--@tablename varchar(20),
--@pageSize int,
--@page int
--AS
--declare @newspage int,
--@res varchar(100)
--begin
--set @newspage=@pageSize*(@page - 1)
--set @res='select * from ' +@tablename+ ' order by [SID] offset '+CAST(@newspage as varchar(10)) +' rows fetch next '+ CAST(@pageSize as varchar(10)) +' rows only'
--exec(@res)
--end

--EXEC page_Demo @tablename='dbo.[PCR2_ExtendFields]',@pageSize=10,@page=1