sqlserver 分頁

標籤：

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

sqlserver 分頁