/*
名称:PagingRecord
作用:按任意字段进行排序分页
作者:谢忠领
时间:2007-2-1
声明:此代码你可以无偿使用及转载,但在转载时,请勿移称本文字声明
*/
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
