--person created: Zengfanlong--Creation time: the-7- - Ten:Wuyi: the--Note: According to the company shorthand code to obtain the current gas cylinder profile data to be synchronized (page get) ALTER PROCEDURE [Up_gasbottles_getsyncdata_bypage] (@CompanyAbbrCode NVARCHAR ( 255) ="', @LatastRowVersion_BigInt BigInt, @CurrentMaxRowVersion_BigInt BigInt, @StartPageIndex INT=0, @EndPageIndex INT=0, @TotalCount INT OUTPUT) As BEGIN--(1), define SQL query SELECT*into #tempTb from dbo. Gasbottles as GS with (NOLOCK) WHERE ISNULL (Gasbottleno,"') <>"'and REPLACE ((SUBSTRING (GS. Gasbottleno,1,5) ),'-',"') =@companyAbbrCode and (CAST ([RowVersion] as BIGINT)>@LatastRowVersion_BigInt and CAST ([RowVersion] as BigInt)<=@CurrentMaxRowVersion_BigInt) IF (@StartPageIndex<=0and @EndPageIndex<=0) BEGIN SELECT*From #tempTb--returns the total number of pages SET @TotalCount= (SELECT COUNT (1) from #tempTb) END ELSE BEGIN--paging Get Data SELECT row_number () over (ORDER by GETDATE ()) as'Row' , *Into #tempAll from #tempTb SELECT*From #tempAll WHERE Row between @StartPageIndex and @EndPageIndex TRUNCATE table #tempAll DROP table #tempAll END--Delete history table TRUNCATE table #tempTb DROP table #tempTb Endgo
SQL example of paged query through stored procedure