sql 批量数据查询分页

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

ALTER PROCEDURE [dbo].[Proc_photolist]
(
@maximumRows int,
@startRowIndex int,
@usid int,
@ptzpid int,
@ptxcid int,
@ptdqid int,
@ptname varchar(50)
)
as
declare @strsql varchar(800)
declare @strwhere varchar(200)
if @ptname is null or @ptname=''
begin
set @strsql='select ptid,ptusid,ptzpid,ptdqid,ptxcid,ptname,ptsmpath,ptbgpath,ptaddtime,ptrq,pqjf,ptadstat,usid,uslinse,usnikname,ustname  from (select ROW_NUMBER()Over(order by ptid desc) as rowId,* from View_Photo'
set @strwhere=''
if @usid<>0
set @strwhere=@strwhere+' and ptusid='+str(@usid)
if @ptxcid<>0
set @strwhere=@strwhere+' and ptxcid='+str(@ptxcid)
if @ptzpid<>0
set @strwhere=@strwhere+' and ptzpid='+str(@ptzpid)
if @ptdqid<>0
set @strwhere=@strwhere+' and ptdqid='+str(@ptdqid)
if @strwhere<>''
begin
set @strwhere=substring(@strwhere,5,len(@strwhere))
set @strsql=@strsql+' where '+@strwhere
end
set @strsql=@strsql+') as mytable where rowId between '+str(@startRowIndex+1)+' and '+str(@startRowIndex+@maximumRows)
execute(@strsql)
end
else
begin
set @strsql='select ptid,ptusid,ptzpid,ptdqid,ptxcid,ptname,ptsmpath,ptbgpath,ptaddtime,ptrq,pqjf,ptadstat,usid,uslinse,usnikname,ustname  from (select ROW_NUMBER()Over(order by ptid desc) as rowId,* from View_Photo'
set @strwhere=''
if @usid<>0
set @strwhere=@strwhere+' and ptusid='+str(@usid)
if @ptxcid<>0
set @strwhere=@strwhere+' and ptxcid='+str(@ptxcid)
if @ptzpid<>0
set @strwhere=@strwhere+' and ptzpid='+str(@ptzpid)
if @ptdqid<>0
set @strwhere=@strwhere+' and ptdqid='+str(@ptdqid)
if @ptname <> ''
set @strwhere=@strwhere+' and ptname like ''%'+@ptname+'%'''
if @strwhere<>''
begin
set @strwhere=substring(@strwhere,5,len(@strwhere))
set @strsql=@strsql+' where '+@strwhere
end
set @strsql=@strsql+') as mytable where rowId between '+str(@startRowIndex+1)+' and '+str(@startRowIndex+@maximumRows)
execute(@strsql)
end