none
这个intsert的事务怎么写? RRS feed

  • 问题

  •    数据库地址 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# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2014年4月22日 9:19

答案

  • 这里不需要事务吧,按照你 逻辑 判断不存在的时候才insert,根本不需要多此一举 加事务 回滚,

    declare @cid int
    set @cid=2
    BEGIN TRAN 
    IF (SELECT  ReceivableID,PropertyIDR   在ReceivableTable不存在)

    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

    2014年4月23日 1:31

全部回复

  • 建议你看下联机帮助上关于 create procedure 的语法说明

    create procedure Pro_AddExistConToRe

    @cid int --- 参数是放在这里面的, 放在 as 后面的是变量, 不是参数
    as
    -- declare @cid int
    --set @cid=2

    2014年4月23日 1:11
  • 这种问题先看一下存储过程的语法,你的存储过程没有定义参数,但是调用的时候使用了参数当然会报错。这个错误已经很清楚了,自己网上搜一下就可以解决。

    Please Mark As Answer if it is helpful.

    2014年4月23日 1:19
  • 这样写

    CREATE PROCEDURE Pro_AddExistConToRe

    @cid INT AS 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



    2014年4月23日 1:24
  •     第二个问题已解决,谢谢!关键是第一个问题该如何解决啊!?

    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2014年4月23日 1:26
  • 这里不需要事务吧,按照你 逻辑 判断不存在的时候才insert,根本不需要多此一举 加事务 回滚,

    declare @cid int
    set @cid=2
    BEGIN TRAN 
    IF (SELECT  ReceivableID,PropertyIDR   在ReceivableTable不存在)

    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

    2014年4月23日 1:31
  •     对哦!太感谢了!

    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2014年4月23日 2:48