CREATE PROCEDURE [dbo].[PR_ListPage]
@tblName varchar(255), -- 表名
@OrderName varchar(255), -- 排序字段名
@Fields varchar(1000) = ' * ', -- 返回的字段的集合
@PageSize int = 20, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@StrOrders varchar(300) = '', -- 设置排序规则
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(1000) -- 主语句
declare @strTmp varchar(300) -- 临时变量
declare @StrOrder varchar(300) -- 临时变量
if @StrOrders != ''
begin
set @StrOrder=' order by ' + @StrOrders
end
set @strTmp = 'not in (select top ' + str(@PageSize-1)
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @Fields+' from ' + @tblName + ' where ' + @OrderName + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @OrderName + ' from ' + @tblName + ' ' + @strOrder + ')' + @strOrder
if @strWhere != ''
begin
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @Fields+' from ' + @tblName + ' where ' + @OrderName + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @OrderName + ' from ' + @tblName + ' where (' + @strWhere + ') ' + @strOrder + ') and (' + @strWhere + ') ' + @strOrder
end
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @Fields+ ' from ' + @tblName + ' ' + @strTmp + ' ' + @strOrder
end
if @iscount !=0
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select count(*) as Total from ' + @tblName + ' '+ @strTmp
end
exec (@strSQL)
@tblName varchar(255), -- 表名
@OrderName varchar(255), -- 排序字段名
@Fields varchar(1000) = ' * ', -- 返回的字段的集合
@PageSize int = 20, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@StrOrders varchar(300) = '', -- 设置排序规则
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(1000) -- 主语句
declare @strTmp varchar(300) -- 临时变量
declare @StrOrder varchar(300) -- 临时变量
if @StrOrders != ''
begin
set @StrOrder=' order by ' + @StrOrders
end
set @strTmp = 'not in (select top ' + str(@PageSize-1)
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @Fields+' from ' + @tblName + ' where ' + @OrderName + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @OrderName + ' from ' + @tblName + ' ' + @strOrder + ')' + @strOrder
if @strWhere != ''
begin
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @Fields+' from ' + @tblName + ' where ' + @OrderName + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ' + @OrderName + ' from ' + @tblName + ' where (' + @strWhere + ') ' + @strOrder + ') and (' + @strWhere + ') ' + @strOrder
end
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @Fields+ ' from ' + @tblName + ' ' + @strTmp + ' ' + @strOrder
end
if @iscount !=0
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select count(*) as Total from ' + @tblName + ' '+ @strTmp
end
exec (@strSQL)