最通用的分页存储过程
来源:互联网 发布:三个矩阵乘法结合律 编辑:程序博客网 时间:2024/04/29 03:21
-
-
-
-
- CREATE PROCEDURE usp_Pagination
- @tblName varchar(255),
- @strGetFields varchar(1000) = '*',
- @fldName varchar(255)='',
- @PageSize int = 10,
- @PageIndex int = 1,
- @doCount bit = 0,
- @OrderType bit = 0,
- @strWhere varchar(1500) = ''
- AS
- declare @strSQL varchar(5000)
- declare @strTmp varchar(110)
- declare @strOrder varchar(400)
- if @doCount != 0
- begin
- if @strWhere !=''
- set @strSQL = 'select count(*) as Total from ['+ @tblName +'] where '+ @strWhere
- else
- set @strSQL = 'select count(*) as Total from ['+ @tblName +']'
- end
-
-
- else
- begin
- 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
- end
- exec ( @strSQL)
- GO<PRE></PRE>