存储过程示例

 create proc [dbo].[Proc_CreateVirtaulContractPro]
@companyID uniqueidentifier--公司ID
as
begin
    --模板合同ID    
    declare @oldContractID uniqueidentifier
    --模板合同第一个合同产品的有效时间
    declare @begin datetime
    declare @end datetime
    SELECT top 1 @begin=p.begindate,@end=p.enddate
    FROM [dbo].[Contract] c
    inner join ContractProduct cp on cp.contractid=c.contractid
    inner join product p on p.ProductID=cp.ProductID
    where c.companyid='00000000-0000-0000-0000-000000000000' and c.ContractType=2
    --找到指定合同ID
    set @oldContractID= (select top 1 ContractID
    from [Contract]
    where companyid='00000000-0000-0000-0000-000000000000' and ContractType=2 )
    declare @newContractID uniqueidentifier
    --产品新合同ID    
    set @newContractID=newid()
    --复制指定合同信息,并更新合同ID,公司ID
    insert  [Contract](ContractID, CompanyID, ContractCode, ContractName, ContractMoney, SignDate, BeginDate, EndDate, CreateDate, ContractType, AuditingState, IsPosting, IsCertified, OriginalContractMoney, Discount, Status, SalesID, BelongTo)
    select top 1 @newContractID,@companyID, ct.ContractCode, ct.ContractName, ct.ContractMoney, ct.SignDate,
           @begin, @end, getdate(), ct.ContractType, ct.AuditingState, ct.IsPosting, ct.IsCertified,
           ct.OriginalContractMoney, ct.Discount, 2, ct.SalesID, ct.BelongTo     
    from [Contract] ct where ContractID=@oldContractID    
    ---------------------------合同产品
    insert ContractProduct(ID, ProductID, ContractID, Discount, DiscountedPrice, BeginDate, EndDate, IsStoped)
    select newid(), cp.ProductID, @newContractID, cp.Discount, cp.DiscountedPrice,
    getdate(), DATEADD(day,p.ProductFloatCyc,getdate()),cp.IsStoped    
    from ContractProduct cp
    inner join product p on p.ProductID=cp.ProductID
    where cp.ContractID=@oldContractID
    ---------------------------合同产品对应该公司拥有的服务项目
    insert CompanyServices(CompanyServiceID, CompanyID, ContractID, ProductID, ServiceID, ServiceNum, Properties, BeginDate, EndDate, ProductType, Status)
    select newid(), @companyID, @newContractID,
    ps.ProductID, ps.ServiceID, ps.BuyServiceNum, ps.Properties,CONVERT(varchar(12) , cp.BeginDate, 110 ),
    CONVERT(varchar(12) , cp.EndDate, 110 ),1,1
    from ContractProduct cp
    inner join Product p on cp.ProductID=p.ProductID            --合同产品VS产品表
    inner join ProductService ps on ps.ProductID=cp.ProductID    --产品VS产品服务表
    where cp.ContractID=@newContractID--'50D84591-EDF0-40B4-9E98-05585447BA72'     
    ---------------------------合同附加产品对应该公司拥有的服务项目
    insert CompanyServices(CompanyServiceID, CompanyID, ContractID, ProductID, ServiceID, ServiceNum, Properties, BeginDate, EndDate, ProductType, Status)
    select newid(), @companyID, @newContractID,
    cap.AttachedProductID,ap.ServiceID, cap.Quantity, 0,CONVERT(varchar(12) , ct.BeginDate, 110 ),
    CONVERT(varchar(12) , ct.EndDate, 110 ),2,1
    from [Contract] ct
    inner join ContactAttachedProduct cap on ct.ContractID=cap.ContractID
    inner join AttachedProduct ap on cap.AttachedProductID=ap.id--合同附加产品VS附加产品表
    where ct.ContractID=@newContractID
    
end