超级通用型分页存储过程(转帖)
--------------------------------------
-- 超级通用型分页存储过程
-- 可对Select 语句进行分页，可带 order 、group 等子句
-- KeyField （关键词） 可以为空，也可以由多个，用，间隔
-- 使用Keyfield 可以进行缓冲更新模式
-- Dcopyboy 义乌科创计算机有限公司 软件部
-- 2012-01-08
---------------------------------------
CREATE proc GetData
@SQLSTR varchar(8000),
@KeyField varchar(500),
@PageNo int,
@PageSize int
as
begin
DECLARE @pos int,@SQLSTR1 varchar(8000),@SQLSTR2 varchar(50)
set @SQLSTR = Rtrim(Ltrim(@SQLSTR))
if lower(SUBSTRING (@SQLSTR, 1, 6)) = 'select'
begin
set @SQLSTR =Rtrim(Ltrim(SUBSTRING(@SQLSTR, 7, 8000)))
if SUBSTRING(@SQLSTR, 1, 8) = 'distinct' begin
set @SQLSTR =Rtrim(Ltrim(SUBSTRING(@SQLSTR, 9, 8000)))
if SUBSTRING(@SQLSTR, 1, 4) <> 'top '
begin
set @SQLSTR = 'select distinct top 10000000 ' + @SQLSTR
end
else begin
set @SQLSTR = 'select distinct ' + @SQLSTR
end
end
else begin
if SUBSTRING(@SQLSTR, 1, 4) <> 'top '
begin
set @SQLSTR = 'select top 10000000 ' + @SQLSTR
end
else begin
set @SQLSTR = 'select ' + @SQLSTR
end
end
if @keyField<>''
begin
DECLARE @KeyField1 varchar(500),@field varchar(50),@Cxtj varchar(2000),@Fields varchar(1000)
set @KeyField1=rtrim(ltrim(@KeyField))
set @Cxtj=''
set @Fields=''
set @pos=CHARINDEX (',',@KeyField1)
while @pos>0 begin
set @field=left(@KeyField1,@pos-1)
set @KeyField1=rtrim(ltrim(right(@KeyField1,len(@KeyField1)-@pos)))
set @pos=CHARINDEX ('.',@field)
if @pos>0 begin
set @Cxtj=@Cxtj+'#temp1.'+right(@field,len(@field)-@pos)+'='+@field+' and '
set @Fields=@Fields+right(@field,len(@field)-@pos)+','
end
else begin
set @Cxtj=@Cxtj+'#temp1.'+@field+'='+@field+' and '
set @Fields=@Fields+@field+','
end
set @pos=CHARINDEX (',',@KeyField1)
end
if @KeyField1<>''
begin
set @pos=CHARINDEX ('.',@KeyField1)
if @pos>0 begin
set @Cxtj=@Cxtj+'#temp1.'+right(@KeyField1,len(@KeyField1)-@pos)+'='+@KeyField1+' and '
set @Fields=@Fields+right(@KeyField1,len(@KeyField1)-@pos)+','
end
else begin
set @Cxtj=@Cxtj+'#temp1.'+@KeyField1+'='+@KeyField1+' and '
set @Fields=@Fields+@KeyField1+','
end
end
if Right( @Cxtj,4)='and '
set @Cxtj=left(@Cxtj,len(@Cxtj)-4)
set @pos=CHARINDEX ('where',LOWER(@SQLSTR))
If @pos>0 begin
set @pos=@pos+4
set @SQLSTR1=left(@SQLSTR,@pos)+' EXISTS (select * from #temp1 where rowNumber between @t+1 and @t+@PageSize and '+@Cxtj+') and '+right(@SQLSTR,len
(@SQLSTR)-@pos)
set @SQLSTR2= ' SELECT '+@Fields
end
else RAISERROR ('带关键字段的分页查询必须带where子句!', 16, 1)
end
else begin
set @SQLSTR1=' select * from #temp1 where rowNumber between @t+1 and @t+@PageSize order by rowNumber '
set @SQLSTR2=' SELECT T1.*,'
end
set @SQLSTR= 'declare @PageSize int,@pageno int '+
' declare @RecordCount int,@t int '+
' Set @PageSize='+convert(varchar(10),@PageSize)+
' set @pageno='+convert(varchar(10),@pageno)+
@SQLSTR2+'IDENTITY(INT,1,1) AS rowNumber INTO #temp1 FROM ('+@SQLSTR+') AS T1 left JOIN (select 1 as a) AS T2 ON 1=2'+
' select @RecordCount= @@rowcount '+
' if @pageno<1 select @pageno=1 ' +
' select @T=(@PageNo-1)*@PageSize ' +
' if @T>=@RecordCount set @T=@T-@RecordCount '+@SQLSTR1+
' drop table #temp1 '+
' select @RecordCount as 记录数 '
exec(@SQLSTR)
end
else RAISERROR ('只适合Select带头的语句', 16, 1)
end
GO
下次将发表可以使用Parameters 的参数 超级通用型分页DELPHI 函数（过程），欢迎大家多提意见。
义乌科创计算机有限公司软件部
Dcopyboy
Email：dcopyboy@tom.com
QQ:445235526
