CREATE PROCEDURE [dbo].[Common]
@strTable varchar(30),--表名
@strFields varchar(300),--检索的字段
@strSortField varchar(30),--排序的字段
@PageIndex int =1, --页码
@PgSize int,--每页大小
@doCount bit=0, --返回记录总数,非0则返回
@strWhere varchar(1500)='' --查询条件(注意不带where)
AS
declare @strSQL varchar(5000) --主语句
declare @strTmp varchar(110) --临时变量
declare @strOrder varchar(400) --排序类型
declare @tblName varchar(255) --表名
declare @strGetFields varchar(1000) --需要返回的列
declare @fidName varchar(255) --排序的字段名
declare @PageSize int --页大小
declare @OrderType bit --设置排序类型,非0则降序
set @tblName=@strTable
set @strGetFields=@strFields
set @fidName=@strSortField
set @PageSize=@PgSize
set @OrderType=1
if @doCount !=0 --返回记录总数
begin
if @strWhere != ''
set @strSQL=" select count(1) as Total from ["+@tblName+"] where "+@strWhere
else
set @strSQL=" select count(1) as Total from ["+@tblName+"]"
end
else
begin
if @OrderType !=0
begin
set @strTmp="<( select min "
set @strOrder = " order by ["+@fidName+"] desc"
--如果@OrderType不是0,就执行降序,这句很重要!
end
else
begin
set @strTmp=">( select max "
set @strOrder = " order by ["+@fidName+"] 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
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fidName + "]" + @strTmp + "(["+ @fidName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fidName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fidName + "]" + @strTmp + "(["
+ @fidName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["
+ @fidName + "] from [" + @tblName + "] where " + @strWhere + " "
+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder
end
end
exec(@strSQL)
GO
高效储存过程分页
最新推荐文章于 2022-02-22 17:32:20 发布