SQL笔记__分页存储过程

setANSI_NULLSON
setQUOTED_IDENTIFIERON
go


--分页存储过程ByTB_TIR_InitialTobacco_Pattrern_NIC
--Table变量写法
--PoweredBy:CHENQP
--参数说明:@pagesize每页要取的记录数
--@pageindex第几页
--@docount标志true则取记录数,false则返回记录集
--调用说明:例如:第一页取8条记录集
--EXECUTEusp_TBTIRInitialTobaccoPattrernNIC_ByPager1,8,false
ALTERprocedure[dbo].[usp_TBTIRInitialTobaccoPattrernNIC_ByPager]
(@pagesizeint,
@pageindexint,
@docountbit)
as
setnocounton
if(@docount=1)
selectcount(I_SortID)fromTB_TIR_InitialTobacco_Pattrern_NIC
else
begin
declare@indextabletable(idintidentity(1,1),nidint)
declare@PageLowerBoundint
declare@PageUpperBoundint
set@PageLowerBound=(@pageindex-1)*@pagesize
set@PageUpperBound=@PageLowerBound+@pagesize
setrowcount@PageUpperBound
insertinto@indextable(nid)
selectI_SortIDfromTB_TIR_InitialTobacco_Pattrern_NIC
orderbyI_SortIDdesc

selectO.*fromTB_TIR_InitialTobacco_Pattrern_NICO,@indextablet
whereO.I_SortID=t.nid
andt.id>@PageLowerBoundandt.id<=@PageUpperBoundorderbyt.id
end
setnocountoff