分页的存储过程

来源：互联网发布：淘宝发布食用农产品编辑：程序博客网时间：2024/05/19 23:16

手写分页很麻烦，今天看了个分页的存储过程，觉得不错，自己又写了一下

--分页的存储过程
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,''