ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[GetRecordByPage]') andOBJECTPROPERTY(id, N'IsProcedure') =1) dropprocedure[dbo].[GetRecordByPage] GO CREATEPROCEDURE GetRecordByPage @tblNamevarchar(255), -- 表名 @fldNamevarchar(255), -- 主键字段名 @PageSizeint=10, -- 页尺寸 @PageIndexint=1, -- 页码 @OrderTypebit=0, -- 设置排序类型, 非 0 值则降序 @strWherevarchar(1000) ='', -- 查询条件 (注意: 不要加 where) @IsReCountbit=0, -- 返回记录总数, 非 0 值则返回 @FieldListvarchar(200) -- 返回的字段 AS declare@strSQLvarchar(6000) -- 主语句 declare@strTmpvarchar(100) -- 临时变量 declare@strOrdervarchar(400) -- 排序类型 if@PageIndex=0 begin set@PageIndex=1 end if@OrderType!=0 begin set@strTmp='<(select min' set@strOrder=' order by ['+@fldName+'] desc' end else begin set@strTmp='>(select max' set@strOrder=' order by ['+@fldName+'] asc' end if@strWhere!=''---条件不为空 set@strSQL='select top '+str(@PageSize) +''+@FieldList+' from [' +@tblName+'] where ['+@fldName+']'+@strTmp+'([' +@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' [' +@fldName+'] from ['+@tblName+'] where '+@strWhere+'' +@strOrder+') as tblTmp) and '+@strWhere+''+@strOrder else---条件为空 set@strSQL='select top '+str(@PageSize) +''+@FieldList+' from [' +@tblName+'] where ['+@fldName+']'+@strTmp+'([' +@fldName+']) from (select top '+str((@PageIndex-1)*@PageSize) +' [' +@fldName+'] from ['+@tblName+']'+@strOrder+') as tblTmp)' +@strOrder set@strTmp='' if@strWhere!='' set@strTmp=' where '+@strWhere if@PageIndex=1 begin set@strSQL='select top '+str(@PageSize) +''+@FieldList+' from [' +@tblName+']'+@strTmp+''+@strOrder end if@IsReCount!=0 set@strSQL='select count(*) as Total from ['+@tblName+']'+@strTmp exec (@strSQL)