通用分页存储过程的实现

1.适合于：主键为int类型的表的分页查询 

ALTER PROC [dbo].[PAGE_KEY_INT] 
@PAGESIZE INT,  --页大小 
@PAGEINDEX INT  --页数 
AS 
declare @sqlStr varchar(5000) 
BEGIN 
IF @PAGEINDEX<=1 
SET @sqlStr = 'SELECT TOP ' + convert(VARCHAR,@PAGESIZE) + ' * FROM people WHERE id > 0 ORDER BY id' 
ELSE 
SET @sqlStr = 'SELECT TOP ' + convert(VARCHAR,@PAGESIZE) + ' * FROM people WHERE 
(id > 
(SELECT MAX(id) FROM 
(SELECT TOP ('+convert(VARCHAR,@PAGESIZE*(@PAGEINDEX-1))+') id FROM people ORDER BY id) AS T)) 
ORDER BY id' 
exec(@sqlStr) 
END 

2.适合于所有表 

ALTER PROC [dbo].[PAGE_KEY_ALL] 
@PAGESIZE INT,  --页大小 
@STARTINDEX INT  --开始位置 
AS 
BEGIN 
DECLARE @SQLSTR VARCHAR(5000) 
SET @SQLSTR = 'SELECT * FROM (SELECT (ROW_NUMBER() OVER (order by ID)) IND, * FROM PEOPLE) A 
WHERE A.IND BETWEEN ' + convert(VARCHAR,@STARTINDEX+1) + ' and ' + convert(VARCHAR,@STARTINDEX+@PAGESIZE) 
EXEC(@SQLSTR) 
END


调用：

EXEC PAGE_KEY_ALL 5,1   --调用PAGE_KEY_INT  EXEC PAGE_KEY_ALL 5,0   --调用PAGE_KEY_ALL