--创建测试表
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([Col] NVARCHAR(10))
--填充数据
INSERT tb
SELECT TOP 26 col1=CHAR(64+ROW_NUMBER()OVER(ORDER BY GETDATE()))
FROM sys.objects
--创建分页的存储过程
IF OBJECT_ID('[sp_page]') IS NOT NULL
DROP PROC [sp_page]
GO
CREATE PROC sp_page
@tablename NVARCHAR(50),--表名
@pagerow INT=0,--每页显示的行数(0为全部)
@pagenow INT=1, --要显示第几页(默认为)
@ordercol VARCHAR(50)=NULL, --排序字段
@order BIT=0 --排序规则0顺序,1倒序
AS
DECLARE @s NVARCHAR(MAX)
SET @s='
WITH t AS
(
SELECT
rownum=ROW_NUMBER()OVER(ORDER BY '+ISNULL(@ordercol,'GETDATE()')+
CASE @order WHEN 1 THEN ' desc' ELSE ' asc' END+'),
*
FROM '+@tablename +'
)
SELECT *
FROM t'
IF @pagerow>0
SET @s=@s+'
WHERE rownum BETWEEN '+LTRIM(@pagerow*(@pagenow-1)+1)+'
AND '+LTRIM(@pagerow*@pagenow)
EXEC(@s)
GO
--返回tb表每页行第页的记录