SQL Server Paging

create PROCEDURE [dbo].[GetPageDataOutRowNumber] ( @tn nvarchar(30), --表名称 @idn nvarchar(20), --表主键名称 @pi int = 1, --当前页数 @ps  int = 7, --每页大小 @wh nvarchar(255) = ‘‘ , --wehre查询条件 @oby nvarchar(255) = ‘‘ , --orderby 排序 @rc int output , --总行数(传出参数) @pc int output --总页数(传出参数) ) AS DECLARE @sql NVARCHAR(225)= ‘‘ ,@sqlCount NVARCHAR(225)= ‘‘ --1.计算总行数和总页数 SET @sqlCount = ‘SELECT @rc=COUNT([‘ [email protected]+ ‘]),@pc=CEILING((COUNT(‘ [email protected]+ ‘)+0.0)/‘ + CAST (@ps AS VARCHAR )+ ‘) FROM ‘ + @tn IF LEN(@wh)>1      set @[email protected]+ ‘ WHERE ‘ [email protected] print @sqlCount EXEC SP_EXECUTESQL @sqlCount,N ‘@rc INT OUTPUT,@pc INT OUTPUT‘ ,@rc OUTPUT ,@pc OUTPUT --2.分页 --2.1如果是第一页，则直接查询 IF @pi = 1 BEGIN      SET @sql= ‘SELECT TOP ‘ +str(@ps) + ‘ * FROM ‘ [email protected]      IF LEN(@wh)>1          set @[email protected]+ ‘ WHERE ‘ +[email protected]      IF LEN(@oby)>1              SET @[email protected]+ ‘ order by ‘ [email protected] EXEC (@sql) END ELSE --2.2如果不是第一页，则拼接查询语句 BEGIN      SET NOCOUNT ON      SET @sql= ‘SELECT * FROM (select row_number() over(order by ‘      IF LEN(@oby)>1          set @[email protected] + @oby+ ‘) as rowNum,* from ‘ [email protected]      else          set @[email protected] + @idn+ ‘) as rowNum,* from ‘ [email protected]      IF LEN(@wh)>1          set @[email protected]+ ‘ where ‘ [email protected]      set @[email protected]+ ‘)as temp where rowNum>‘ +str(@ps * (@pi-1))+ ‘ and rowNum<=‘ +str(@ps*@pi)      print @sql      EXEC (@sql)      SET NOCOUNT OFF END --测试语句 declare @rc int ,@pc int exec [GetPageDataOutRowNumber] ‘Ams_Area‘ , ‘ar_id‘ ,2,5, ‘‘ , ‘ ar_id desc‘ ,@rc output ,@pc output select @rc,@pc

SQL Server Paging