分页的存储过程

  --分页的存储过程 CREATE PROCEDURE paging ( --表名 @tblName varchar(64), --要查询的字段 @strGetFields varchar(256) = '', --按那个字段排序 @fldName varchar(64)='', --每页多少条记录 @PageSize int = 20, --第几页 @PageIndex int = 1, --排序方式0表示倒序，1表示顺序 @OrderType bit = 1, --where条件 @strWhere varchar(256) = '' ) AS BEGIN declare @strSQL varchar(1000) declare @strTmp varchar(110) declare @strOrder varchar(400) SET NOCOUNT ON 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 @PageIndex = 1 begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @tblName + ' '+ @strOrder end else begin set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from ' + @tblName + ' ' + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder end EXEC (@strSQL) if @@error=0 return 1 SET NOCOUNT OFF END GO exec paging 'news','*','id',3,2,0,''