CREATE procedure [dbo].[Prc_splitpage]
@sql nvarchar(4000),
@idField nvarchar(100),
@page int=1,
@pageSize int,
@pageCount int=0 out,
@recordCount int=0 out,
@where nvarchar(4000),
@order nvarchar(4000)
as
declare @sqlLen int
declare @sqlWhereIndex int
declare @sqlWhere nvarchar(4000)
declare @sqlOrderIndex int
declare @sqlOrder nvarchar(4000)
declare @sqlBracketIndex int
declare @sqlFromIndex int
declare @leftBracketNumber int
declare @rightBracketNumber int
set @sqlWhere=''
set @sqlOrder=''
set @sqlWhereIndex=-1
set @sqlOrderIndex=-1
set @sqlFromIndex=-1
select @sql=LOWER(@sql)
select @sqlLen=len(@sql)
while (charindex(' order by ',@sql,@sqlOrderIndex+1)>0)
begin
set @sqlOrderIndex=charindex(' order by ',@sql,@sqlOrderIndex+1)
end
set @sqlBracketIndex=@sqlOrderIndex
set @leftBracketNumber=0
set @rightBracketNumber=0
while (charindex('(',@sql,@sqlBracketIndex+1)>0)
begin
set @leftBracketNumber=@leftBracketNumber+1
set @sqlBracketIndex=charindex('(',@sql,@sqlBracketIndex+1)
end
set @sqlBracketIndex=@sqlOrderIndex
while (charindex(')',@sql,@sqlBracketIndex+1)>0)
begin
set @rightBracketNumber=@rightBracketNumber+1
set @sqlBracketIndex=charindex(')',@sql,@sqlBracketIndex+1)
end
if(@leftBracketNumber!=@rightBracketNumber) set @sqlOrderIndex=-1
if(@sqlOrderIndex>0)
begin
select @sqlOrder=substring(@sql,@sqlOrderIndex,@sqlLen)
select @sql=substring(@sql,0,@sqlOrderIndex)
end
else
begin
set @sqlOrder=@order
end
while (charindex(' where ',@sql,@sqlWhereIndex+1)>0)
begin
set @sqlWhereIndex=charindex(' where ',@sql,@sqlWhereIndex+1)
end
set @sqlBracketIndex=@sqlWhereIndex
set @leftBracketNumber=0
set @rightBracketNumber=0
while (charindex('(',@sql,@sqlBracketIndex+1)>0)
begin
set @leftBracketNumber=@leftBracketNumber+1
set @sqlBracketIndex=charindex('(',@sql,@sqlBracketIndex+1)
end
set @sqlBracketIndex=@sqlWhereIndex
while (charindex(')',@sql,@sqlBracketIndex+1)>0)
begin
set @rightBracketNumber=@rightBracketNumber+1
set @sqlBracketIndex=charindex(')',@sql,@sqlBracketIndex+1)
end
if(@leftBracketNumber!=@rightBracketNumber) set @sqlWhereIndex=-1
if(@sqlWhereIndex>0)
begin
select @sqlWhere=substring(@sql,@sqlWhereIndex,@sqlLen)
select @sql=substring(@sql,0,@sqlWhereIndex)
if(len(isnull(@where,'')) >0)
begin
select @sqlWhere=@sqlWhere+' and '+@where
end
end
else
begin
if(len(isnull(@where,'')) >0)select @sqlWhere=' where '+@where
end
while (charindex(' from',@sql,@sqlFromIndex+1)>0)
begin
set @sqlFromIndex=charindex(' from',@sql,@sqlFromIndex+1)
end
set @sqlBracketIndex=@sqlFromIndex
set @leftBracketNumber=0
set @rightBracketNumber=0
while (charindex('(',@sql,@sqlBracketIndex+1)>0)
begin
set @leftBracketNumber=@leftBracketNumber+1
set @sqlBracketIndex=charindex('(',@sql,@sqlBracketIndex+1)
end
set @sqlBracketIndex=@sqlFromIndex
while (charindex(')',@sql,@sqlBracketIndex+1)>0)
begin
set @rightBracketNumber=@rightBracketNumber+1
set @sqlBracketIndex=charindex(')',@sql,@sqlBracketIndex+1)
end
if(@leftBracketNumber!=@rightBracketNumber) set @sqlFromIndex=-1
if(@idField is not null and len(@idField)>0)
begin
declare @fromSql nvarchar(4000)
declare @totalSql nvarchar(4000)
declare @totalnum int
if(@sqlOrder is null or len(@sqlOrder)=0) select @sqlOrder=' order by '+@idField
select @fromSql=substring(@sql,@sqlFromIndex,@sqlLen)
select @totalSql='select @totalnum=count(1) '+@fromSql+@sqlWhere
exec sp_executesql @totalSql,N'@totalnum int output',@totalnum output
set @pageCount=@totalnum
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize),@page=(@page-1)*@pagesize+1
select @sql='select A.*,B.rowNum from ('+@sql+@sqlWhere+') A,(select ROW_NUMBER() Over('+@sqlOrder+') as rowNum, '+@idField+@fromSql+@sqlWhere+') B where B.rowNum>='+cast(@page as varchar)+' and B.rowNum<'+cast((@page+@pagesize) as varchar)+' and B.'+@idField+'=A.'+@idField+' order by B.rowNum'
set nocount on
--使用游标
declare @p3 int
exec sp_cursoropen @p3 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
exec sp_cursorfetch @p3,16,1,@pagesize
exec sp_cursorclose @p3
end
else
begin
select @sql=@sql+@sqlWhere+@sqlOrder
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize),@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
end
调用：
DECLARE
@pageCount int,
@recordCount int
EXEC [Prc_splitpage]
@sql = N'select a.* from glunimportreceivablelistsubps(2,1,''Y'',''01/01/2011'',''10/31/2011'')a ',
@idField = 'mainid',
@page = 1,
@pageSize = 20,
@pageCount = @pageCount OUTPUT,
@recordCount = @recordCount OUTPUT,
@where = N'',
@order = N''
SELECT @recordCount as N'@recordCount'
