ALTER proc [dbo].[AuditSelect] --修改存储过程 alter 修改,proc 存储过程简写,后面的是存储过程名 @chrXML ntext --存储过程参数 as begin --sql语句块 declare @iDoc int --声明变量 declare @iType int declare @work_ID varchar(8) declare @iPage int declare @pageSize int declare @iCount int declare @PageCount int declare @i int declare @where varchar(1000) declare @sql nvarchar(4000) declare @uid int declare @Day varchar(6) declare @Audit_id int exec sp_xml_preparedocument @iDoc output , @chrXML --执行系统存储过程 select @iType=type,@iPage=iPage,@pageSize=pageSize,@PageCount=PageCount,@iCount=iCount, @Audit_id=Audit_id from openxml(@iDoc,'/r',1) with(type int ,iPage int,pageSize int,iCount int,PageCount int,Audit_id int) begin set @where = ' where 1=1 ' if isnull(@Audit_id,0) <>0 begin set @where = @where + ' and a.Audit_id='+convert(varchar(10),@Audit_id) end set @sql = 'select @iCount=count(*) from Audit a ' set @sql = @sql + @where exec sp_executesql @sql,N'@iCount int output',@iCount output if @iCount%@pageSize > 0 begin set @PageCount= @iCount/@pageSize+1 set @i = @iCount%@pageSize end else begin set @PageCount= @iCount/@pageSize set @i =@pageSize end set @sql = 'select top '+convert(varchar(10),@iPage*@pageSize)+' a.Audit_id from Audit a '+@where set @sql = @sql+ ' order by a.Audit_id' if @iPage=@PageCount begin set @sql = 'select top '+convert(varchar(10),@i)+' a.Audit_id from (' +@sql +') a order by a.Audit_id ' end else begin set @sql = 'select top '+convert(varchar(10),@pageSize)+' a.Audit_id from (' +@sql +') a order by a.Audit_id' end set @sql = ' select top '+convert(varchar(10),@pageSize)+' a.Audit_id,a.Project_id,b.Project_Name,c.Company_Name as BuildCompany_Name,d.Company_Name as ActualCompany_Name,e.Company_Name as belongCompany_Name,f.Company_Name as DemolitionCompany_Name,g.Company_Name as AssessCompany_Name,c.Company_id,b.BuildCompany,b.ActualCompany,b.BelongCompany,b.DemolitionCompany,b.AssessmentCompany,a.Apply,a.ApplyTime, a.Bank,a.BankAccount,a.PrepaidFunds,a.ActualFunds,a.AdvanceFunds,a.AuditFunds,a.Reason,a.Advice,a.Views,a.Results,a.CreateUser,a.Createtime,a.UpdateTime from Audit a left join Project b on a.Project_id=b.project_id left join Company c on c.Company_id=b.BuildCompany left join Company d on d.Company_id=b.ActualCompany left join Company e on e.Company_id=b.BelongCompany left join Company f on f.Company_id=b.DemolitionCompany left join Company g on g.Company_id=b.AssessmentCompany
where a.Audit_id in ( '+@sql+' ) order by a.Audit_id ' print @sql exec sp_executesql @sql --执行sql语句 select @iPage iPage,@pageSize pageSize,@iCount iCount,@PageCount PageCount end
@chrXML ntext --存储过程参数
as
begin --sql语句块
declare @iDoc int --声明变量
declare @iType int
declare @work_ID varchar(8)
declare @iPage int
declare @pageSize int
declare @iCount int
declare @PageCount int
declare @i int
declare @where varchar(1000)
declare @sql nvarchar(4000)
declare @uid int
declare @Day varchar(6)
declare @Audit_id int exec sp_xml_preparedocument @iDoc output , @chrXML --执行系统存储过程
select @iType=type,@iPage=iPage,@pageSize=pageSize,@PageCount=PageCount,@iCount=iCount,
@Audit_id=Audit_id
from openxml(@iDoc,'/r',1)
with(type int ,iPage int,pageSize int,iCount int,PageCount int,Audit_id int) begin set @where = ' where 1=1 ' if isnull(@Audit_id,0) <>0
begin
set @where = @where + ' and a.Audit_id='+convert(varchar(10),@Audit_id)
end set @sql = 'select @iCount=count(*) from Audit a ' set @sql = @sql + @where exec sp_executesql @sql,N'@iCount int output',@iCount output
if @iCount%@pageSize > 0
begin
set @PageCount= @iCount/@pageSize+1
set @i = @iCount%@pageSize
end
else
begin
set @PageCount= @iCount/@pageSize
set @i =@pageSize
end
set @sql = 'select top '+convert(varchar(10),@iPage*@pageSize)+' a.Audit_id from Audit a '+@where
set @sql = @sql+ ' order by a.Audit_id' if @iPage=@PageCount
begin
set @sql = 'select top '+convert(varchar(10),@i)+' a.Audit_id from (' +@sql +') a order by a.Audit_id '
end
else
begin
set @sql = 'select top '+convert(varchar(10),@pageSize)+' a.Audit_id from (' +@sql +') a order by a.Audit_id'
end
set @sql = ' select top '+convert(varchar(10),@pageSize)+' a.Audit_id,a.Project_id,b.Project_Name,c.Company_Name as BuildCompany_Name,d.Company_Name as ActualCompany_Name,e.Company_Name as belongCompany_Name,f.Company_Name as DemolitionCompany_Name,g.Company_Name as AssessCompany_Name,c.Company_id,b.BuildCompany,b.ActualCompany,b.BelongCompany,b.DemolitionCompany,b.AssessmentCompany,a.Apply,a.ApplyTime,
a.Bank,a.BankAccount,a.PrepaidFunds,a.ActualFunds,a.AdvanceFunds,a.AuditFunds,a.Reason,a.Advice,a.Views,a.Results,a.CreateUser,a.Createtime,a.UpdateTime
from Audit a left join Project b on a.Project_id=b.project_id
left join Company c on c.Company_id=b.BuildCompany
left join Company d on d.Company_id=b.ActualCompany
left join Company e on e.Company_id=b.BelongCompany
left join Company f on f.Company_id=b.DemolitionCompany
left join Company g on g.Company_id=b.AssessmentCompany
where a.Audit_id in ( '+@sql+' ) order by a.Audit_id '
print @sql
exec sp_executesql @sql --执行sql语句
select @iPage iPage,@pageSize pageSize,@iCount iCount,@PageCount PageCount
end