USE [NPU_Organization]
GO
/****** Object: StoredProcedure [dbo].[UP_GetPageList] Script Date: 08/24/2013 11:22:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UP_GetPageList]
(
@tbName varchar(255), -- 表名
@fields varchar(1000) = '*', -- 需要返回的列
@order varchar(255)='1', -- 排序的字段名
@pageSize int = 10, -- 页尺寸
@pageIndex int = 1, -- 页码
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@count int = 0 output, -- 返回记录总数, 非 0 值则返回
@orderType bit = 0 -- 设置排序类型, 非 0 值则降序
)
AS
declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @getvalue varchar(400)
declare @rowOrder varchar(100)
set @strSQL = 'select @count= count(1) from ' + @tbName + ' where 1=1 '+@strWhere
print @strSQL
exec sp_executesql @strSQL,N'@count int out',@Count out
print @count
declare @sql nvarchar(2000)
declare @cou int
begin
if @orderType = 0
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @order +' desc'
set @rowOrder=' row '
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @order +' asc'
set @rowOrder=' row desc '
end
if @PageIndex = 1
begin
print @fields
set @strSQL = 'select top ' + str(@PageSize) +' '+@fields+ ' from ' + @tbName + ' where 1=1 ' + @strWhere + ' ' + @strOrder
--如果是第一页就执行以上代码,这样会加快执行速度
end
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'with tempTb as(select top ' + str(@Count) +' '+@fields+ ' from '
+ @tbName + ' where 1=1 ' +@strWhere+ @strOrder +' )'
set @strSql+='select top '+ str(@PageSize) +' * from tempTb where row '+@strTmp+'(row) from (select top '+ str(@PageSize*(@pageIndex-1)) +' row from tempTb) as row) order by '+@rowOrder
end
end
print @strSQL
exec (@strSQL)