/*
名称:PagingRecord
作用:按任意字段进行排序分页
*/
CREATE PROCEDURE [dbo].[PagingRecord]
(
@PageIndex int,–页号,从0开始
@PageSize int,–页尺寸
@OrderField varchar(100),–排序字段及类型(多个条件用逗号分开)如：JobID DESC,Checkintime
@TableName varchar(100),–表名或视图表
@StrWhere varchar(2000),–条件
@FieldList varchar(2000),–欲选择字段列表
@DoCount AS bit=1– 0值返回记录总数, 非 0 值则返回记录
)
AS
BEGIN TRAN
DECLARE @SqlQuery varchar(4000)
IF @DoCount<>0
Goto GetCount
Else
Goto GetSearch
GetCount:–返回记录总数
DECLARE @SearchSql AS Nvarchar(4000)
SET @SearchSql= ‘SELECT Count(*) AS Total FROM ‘+@TableName+’ WHERE ‘+@StrWhere
exec sp_executesql @SearchSql
–print @SearchSql
COMMIT TRAN
return
GetSearch:
SET @SqlQuery=’SELECT ‘+@FieldList+’
FROM (SELECT row_number() over(ORDER BY ‘+@OrderField+’) as rownum,
‘+@FieldList+’
FROM ‘+@TableName+’ WHERE ‘+@StrWhere +’) as temp
WHERE rownum BETWEEN (‘+cast(@PageIndex as varchar)+’-1)*’+cast(@PageSize as varchar)+’+1 and ‘+cast(@PageIndex
as varchar)+’*’+cast(@PageSize as varchar) + ‘ ORDER BY ‘+@OrderField
–print @SqlQuery
SET NOCOUNT ON
execute(@SqlQuery)
SET NOCOUNT OFF
COMMIT TRAN