/****** 对象: 存储过程 dbo.pro_sys_GetRecordByPage ******/
– =============================================
– Author: <Author,Name>
– Create date: <Create Date,>
– Description: <Description,>
– =============================================
CREATE PROCEDURE [dbo].[pro_sys_GetRecordByPage]
@Source NVARCHAR(max), --表名、视图名、查询语句
@PageSize INT=10, --每页的大小(行数)
@CurrentPage INT=1, --要显示的页
@FieldList NVARCHAR (max) = NULL,–要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@Sort NVARCHAR (max) = NULL, --排序字段列表
@RecordCount INT = NULL OUTPUT, --输出记录数, 如果@Count为null, 则输出记录数, 否则不要输出
@FdName NVARCHAR(max)=NULL --表中的主键或表、临时表中的标识列名
AS

SET NOCOUNT ON --不统计影响行

DECLARE @Id1 NVARCHAR(20), --开始行号
@Id2 NVARCHAR(20), --结束行号
@lbusetemp NVARCHAR(MAX), --临时的sql语句
@Obj_ID INT --是否为表

– 设置开始行号 结束行号
SET @Id1 = (@CurrentPage - 1) * @PageSize+1
SET @Id2 = @CurrentPage*@PageSize

/* --基础设置 begin-- /
select @Obj_ID=OBJECT_ID(@Source)
,@Sort=’ ORDER BY ‘+@Sort
,@FieldList=CASE ISNULL(@FieldList,’’) WHEN ‘’ THEN ’ * ’ ELSE ’ ‘+@FieldList END
,@Source=CASE WHEN @Obj_ID IS NOT NULL THEN ’ ‘+@Source ELSE ’ (’+@Source+’) a ’ END
/ --基础设置 end-- */

SET @lbusetemp=CASE @RecordCount WHEN NULL THEN ‘’ ELSE ‘SELECT @RecordCount=COUNT() FROM ‘+@Source+’;’ END +
N’
/*****/
WITH temptable AS
(
SELECT ROW_NUMBER() OVER(’+@Sort+’) AS [row_number], ‘+@FieldList+’
FROM ‘+@Source+’
)
/******/
SELECT * from temptable
WHERE [row_number] BETWEEN ‘+@Id1+’ AND ‘+@Id2+’;’

EXEC sp_executesql @lbusetemp, N’@RecordCount int out’, @RecordCount OUT

SET NOCOUNT OFF

GO