存储过程分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[GetDataByPager2]
   @startIndex INT,--起始记录数，从0开始
   @tableName VARCHAR(50),
   @pageSize INT=5,
   @condition VARCHAR(1000)='1=1',
   @key VARCHAR(20)='id',
   @orderType VARCHAR(20)='desc'
AS
BEGIN
  DECLARE @TopCount INT
  --SET @TopCount=(@pageIndex-1)*@pageSize
  DECLARE @SQL  VARCHAR(1000)
  SET @SQL='select  TOP ' +CONVERT(VARCHAR(20),@pagesize)+' * FROM '+@tableName
           +' WHERE '+ @condition+' and '+@key+' NOT IN(SELECT TOP  ' +CONVERT(VARCHAR(20),@startIndex)+' '+@key+' FROM '+@tableName+' where '+@condition+' order by '+@key+' '+@orderType+') order by '+@key+' '+@orderType
  --SET @SQL =@SQL+' SELECT COUNT(*) FROM '+@TableName+' WHERE '+@condition
  PRINT(@SQL)
  EXEC(@SQL)
END