CREATE PROCEDURE HarmoniousData(@str nvarchar(2000),@number int) AS
declare @sql nvarchar(4000)
--插入合作企业信息到临时表 #tempEnterprise
if object_id('#tempEnterprise') is not null
drop table #tempEnterprise
SELECT DISTINCT CheckName, CheckApprovalNo, CheckEnterprise, typename into #tempEnterprise
FROM T_Enterprise a,(select typeid,typename from t_type where themeid=2)b
WHERE (EnterpriseId IN
(SELECT id
FROM T_ContractEnterprise
WHERE (IDType = 1) AND ([PERCENT] = 100) AND isstop = 0 ))
and a.typeid=b.typeid
exec(' if object_id(''#tempEnterpriseResult'') is not null
drop table #tempEnterpriseResult
select a.reportid,a.checkname,a.playdate,a.checkapprovalno,a.checkenterprise,a.typename,a.channelno into #tempEnterpriseResult
from t_result a,#tempEnterprise b
where '+@str+'
and a.checkname=b.checkname and a.checkapprovalno=b.checkapprovalno and a.checkenterprise=b.checkenterprise and a.typename=b.typename')
exec('SELECT * FROM T_Result a,#tempEnterprise b
WHERE '+@str+'
and a.checkname=b.checkname and a.checkapprovalno=b.checkapprovalno and a.checkenterprise=b.checkenterprise and a.typename=b.typename
and reportid not in (select reportid from #tempEnterpriseResult a where
(select count(1) from #tempEnterpriseResult pp where --a.reportid=pp.reportid and
a.checkname=pp.checkname and a.playdate=pp.playdate and a.checkapprovalno=pp.checkapprovalno and a.checkenterprise=pp.checkenterprise
and a.typename=pp.typename
and a.channelno>pp.channelno)<'+@number+')')
GO