这个intsert的事务怎么写？
问题
-
数据库地址 https://skydrive.live.com/?cid=10ae47740a646d6e
数据库中有三个表，分别是contractTable合同表、PropertyTable资产表和ReceivableTable收入表。把contractTable和PropertyTable用jion on 联合起来，再根据条件把符合条件的数据insert到ReceivableTable中。下面的代码1实现了这个功能。
我希望写一个事务，如果下面代码中 ReceivableID,PropertyIDR 已经在ReceivableTable中存在，那么事务回滚,不insert。反之则insert。尝试了半天没成功，请大家帮忙看看！
另外，这段代码我写了个存储过程，可为什么执行出错呢，见代码2和图1？
-------------------------------------- 代码1 ----------------------------------------------
declare @cid int set @cid=2 insert into ReceivableTable (ReceivableID,PropertyIDR,ContractNumberR, PropertyCityR,PropertyRoadR,PropertyAddressR,PropertyCRAR, LeaseDateFromR,LeaseDateToR, MonthRent, OppositeSideR ,ManageProjectR,BelongToCompanyR ,ReceivableState ) select YEAR(GETDATE()),b.PropertyID, ContractNumber , b.City ,b.Road ,b.Address , b.City+b.Road+b.Address, a.LeaseDateFrom,a.LeaseDateTo, a.MonthRent , a.OppositeSide ,a.ManageProject,b.BelongToCompany ,1 FROM (PropertyTable b join ContractTable a on b.PropertyID =a.ContractID ) where b.PropertyState='1' and a.ContractType='租赁' and a.PerformState='正在履行' and CID=@cid
------------------------------------- 代码2------------------------------------
create procedure Pro_AddExistConToRe as declare @cid int --set @cid=2 insert into ReceivableTable (ReceivableID,PropertyIDR,ContractNumberR, PropertyCityR,PropertyRoadR,PropertyAddressR,PropertyCRAR, LeaseDateFromR,LeaseDateToR, MonthRent, OppositeSideR ,ManageProjectR,BelongToCompanyR ,ReceivableState ) select YEAR(GETDATE()),b.PropertyID, ContractNumber , b.City ,b.Road ,b.Address , b.City+b.Road+b.Address, a.LeaseDateFrom,a.LeaseDateTo, (CASE WHEN a.TypeOfPayment NOT LIKE '%免租%' AND a.TypeOfPayment NOT LIKE '%递增%' THEN MonthRent WHEN a.TypeOfPayment NOT LIKE '%免租%' AND a.TypeOfPayment LIKE '%递增%' THEN (CASE WHEN DATEDIFF(day , a.LeaseDateFrom , dateadd(day , 7 , getdate())) BETWEEN 0 AND DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 1 , leasedatefrom))) THEN MonthRent WHEN DATEDIFF(day , a.LeaseDateFrom , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 1 , leasedatefrom))) + 1 AND DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 2 , leasedatefrom))) THEN MonthRent1 WHEN DATEDIFF(day , a.LeaseDateFrom , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 2 , leasedatefrom))) + 1 AND DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 3 , leasedatefrom))) THEN MonthRent2 WHEN DATEDIFF(day , a.LeaseDateFrom , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 3 , leasedatefrom))) + 1 AND DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 4 , leasedatefrom))) THEN MonthRent3 WHEN DATEDIFF(day , a.LeaseDateFrom , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 4 , leasedatefrom))) + 1 AND DATEDIFF(day , LeaseDateFrom , DATEADD(day , - 1 , DATEADD(year , 5 , leasedatefrom))) THEN MonthRent4 ELSE NULL END) WHEN a.TypeOfPayment LIKE '%免租%' AND a.TypeOfPayment LIKE '%递增%' THEN (CASE WHEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , dateadd(day , 7 , getdate())) BETWEEN 0 AND DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 1 , DATEADD(day , 1 , EscapeLeaseDateTo))) THEN MonthRent WHEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 1 , DATEADD(day , 1 , EscapeLeaseDateTo))) + 1 AND DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 2 , DATEADD(day , 1 , EscapeLeaseDateTo))) THEN MonthRent1 WHEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 2 , DATEADD(day , 1 , EscapeLeaseDateTo))) + 1 AND DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 3 , DATEADD(day , 1 , EscapeLeaseDateTo))) THEN MonthRent2 WHEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 3 , DATEADD(day , 1 , EscapeLeaseDateTo))) + 1 AND DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 4 , DATEADD(day , 1 , EscapeLeaseDateTo))) THEN MonthRent3 WHEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , dateadd(day , 7 , getdate())) BETWEEN DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 4 , DATEADD(day , 1 , EscapeLeaseDateTo))) + 1 AND DATEDIFF(day , DATEADD(day , 1 , EscapeLeaseDateTo) , DATEADD(YEAR , 5 , DATEADD(day , 1 , EscapeLeaseDateTo))) THEN MonthRent4 ELSE NULL END) WHEN a.TypeOfPayment LIKE '%免租%' AND a.TypeOfPayment NOT LIKE '%递增%' THEN (CASE WHEN dateadd(day , 7 , getdate()) >= EscapeLeaseDateFrom THEN MonthRent ELSE NULL END) ELSE NULL END) as mr, a.OppositeSide ,a.ManageProject,b.BelongToCompany ,1 FROM (PropertyTable b join ContractTable a on b.PropertyID =a.ContractID ) where b.PropertyState='1' and a.ContractType='租赁' and a.PerformState='正在履行' and CID=@cid go --drop proc Pro_AddExistConToRe go exec Pro_AddExistConToRe 2
------------------------------------------图1---------------------------------------
C# 菜鸟中的雏鸟！提的问题也许很幼稚，但我是认真的。希望看在党国的面子上拉兄弟一把！
