多表分页查询存储过程

ALTER proc [dbo].[usp_GetMpsListPageNew]
@mpsid varchar(50),--制单号查询条件
@name varchar(50),--款号查询条件
@pagesize int,--页条数
@pageindex int,--当前页码
@recordcount int output--总条数
--@pagecount int output--总页数
as
begin

declare @where varchar(1000)='',@sql nvarchar(4000),@recordsql varchar(1000),@pagecountsql varchar(1000),
@BeginIndex int=@pagesize*(@pageindex-1)+1, @EndIndex int= @pagesize*@pageindex

if(len(@mpsid)>0)
begin
set @where=' and mps.mpsid like ''%'+@mpsid+'%'''
end
if(len(@name)>0)
begin
set @where=@where+'mmf.name like ''%'+@name+'%'''
end
set @sql='
select * from(
Select MPS.mpsid,mpslist.matid mpsmatid,MPS.FillDate,MPS.CustomerID,MPS.TabNM,Mpslist.CustCode,Mpslist.CutCenterID,Mpslist.Qty,Series.BIGTYPE as SeriesNM,Mpslist.ProdLineName,Mpslist.ProdLineType,mpslist.pactid as KeDingDanHao,mpslist.RequireDate,mpslist.LeaveDate,ClothMMF.Code as ClothCode,
ClothMMF.Name as ClothName,mmf.othertype1,MMF.ProPerty,solist.ProdPlace,mmf.bigtype,MMF.Code,MMF.Matid,MMF.Name,MMF.Unit,Provider.Name as ProviderNM,so.season,
Channel.Name ChannelNM,Trantype.Trantype TrantypeNM,rn=ROW_NUMBER() over (order by mps.Filldate desc,mps.mpsid)
from MPS(nolock)
inner Join MPSList(nolock) on (MPS.MPSID=MPSList.MPSID)
left join solist(nolock) on solist.SOID=mpslist.SOID
inner Join MMF(nolock) on (MPSList.Matid=MMF.Matid)
inner Join Customer(nolock) on (MPS.CustomerID=Customer.CustomerID)
Left Join MMF ClothMMF(nolock) on (MPSList.ClothMatid=ClothMMF.Matid)
inner Join Series(nolock) on (MMF.SeriesID=Series.SeriesID)
Left Join Provider(nolock) on (MPSList.ProviderID=Provider.ProviderID)
left join Channel (nolock) on Channel.ChannelID=mps.Channelid
left join Trantype (nolock) on Trantype.TrantypeID=mpslist.TrantypeID
where isnull(mps.BillType,'''')<>''BanMPS'''+@where+'
) a
where a.rn between '+cast(@BeginIndex as varchar)+' and '+cast(@EndIndex as varchar)+''

set @recordsql='
select @recordcount=(Select count(*) from MPS(nolock)
inner Join MPSList(nolock) on (MPS.MPSID=MPSList.MPSID)
left join solist(nolock) on solist.SOID=mpslist.SOID
inner Join MMF(nolock) on (MPSList.Matid=MMF.Matid)
inner Join Customer(nolock) on (MPS.CustomerID=Customer.CustomerID)
Left Join MMF ClothMMF(nolock) on (MPSList.ClothMatid=ClothMMF.Matid)
inner Join Series(nolock) on (MMF.SeriesID=Series.SeriesID)
Left Join Provider(nolock) on (MPSList.ProviderID=Provider.ProviderID)
left join Channel (nolock) on Channel.ChannelID=mps.Channelid
left join Trantype (nolock) on Trantype.TrantypeID=mpslist.TrantypeID
where isnull(mps.BillType,'''')<>''BanMPS'''+@where+') '

set @sql=@sql+@recordsql
print @sql

exec sp_executesql @sql,N'@recordcount int output',@recordcount output

end