IF EXISTS(SELECT * FROM sys.objects WHERE [NAME] = 'Pagination')
DROP PROCEDURE Pagination
GO
CREATE PROCEDURE Pagination
@tblName NVARCHAR(255),-- 表名
@strGetFields NVARCHAR(1000) = '*',-- 需要返回的列
@fldName NVARCHAR(255)='',-- 排序的字段名
@PageSize INT = 10,-- 页尺寸
@PageIndex INT = 1,-- 页码
@doCount BIT = 0,-- 返回记录总数, 非 0 值则返回
@OrderType BIT = 0,-- 设置排序类型, 非 0 值则降序
@strWhere NVARCHAR(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
DECLARE @strSQL NVARCHAR(4000)-- 主语句
DECLARE @strTmp NVARCHAR(110)-- 临时变量
DECLARE @strOrder NVARCHAR(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
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。
--以下的所有代码都是@doCount为0的情况
ELSE
BEGIN
IF @OrderType != 0
BEGIN
SET @strTmp = '<(select min'
SET @strOrder = ' order by ['+ @fldName +'] desc'
--如果@OrderType不是0,就执行降序,这句很重要!
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) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' from ['+ @tblName +'] where ' + @strWhere + ' ' + @strOrder
ELSE
SET @strSQL = 'select top ' + str(@PageSize) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' from ['+ @tblName +'] '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
END
ELSE
BEGIN
--以下代码赋予了@strSQL以真正执行的SQL代码
SET @strSQL = 'select top ' + str(@PageSize) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' 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) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' 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
GO
CREATE PROCEDURE Pagination
@tblName NVARCHAR(255),-- 表名
@strGetFields NVARCHAR(1000) = '*',-- 需要返回的列
@fldName NVARCHAR(255)='',-- 排序的字段名
@PageSize INT = 10,-- 页尺寸
@PageIndex INT = 1,-- 页码
@doCount BIT = 0,-- 返回记录总数, 非 0 值则返回
@OrderType BIT = 0,-- 设置排序类型, 非 0 值则降序
@strWhere NVARCHAR(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
DECLARE @strSQL NVARCHAR(4000)-- 主语句
DECLARE @strTmp NVARCHAR(110)-- 临时变量
DECLARE @strOrder NVARCHAR(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
--以上代码的意思是如果@doCount传递过来的不是0,就执行总数统计。
--以下的所有代码都是@doCount为0的情况
ELSE
BEGIN
IF @OrderType != 0
BEGIN
SET @strTmp = '<(select min'
SET @strOrder = ' order by ['+ @fldName +'] desc'
--如果@OrderType不是0,就执行降序,这句很重要!
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) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' from ['+ @tblName +'] where ' + @strWhere + ' ' + @strOrder
ELSE
SET @strSQL = 'select top ' + str(@PageSize) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' from ['+ @tblName +'] '+ @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
END
ELSE
BEGIN
--以下代码赋予了@strSQL以真正执行的SQL代码
SET @strSQL = 'select top ' + str(@PageSize) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' 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) +' [email=[ft=,2,]'+@strGetFields]'+@strGetFields[/email] + ' 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