一个存储过程实现增删改操作

-------------------------------------------------------------
--作者：Dezai
--时间：2008-5-28
-------------------------------------------------------------
--存储过程的功能：对表 UBS_CAS_PriceFormBatch 进行添加、更新、删除、获取操作。
-------------------------------------------------------------
--参数说明：
-------------------------------------------------------------
/*
@DataAction 添加更新删除的标志位
@ID  ID
@BatchNo  ('批次号') 
@ItemClassID  分类 
@PurchaserUID  分类 
@AuditorUID  批准人 
@CheckerUID  审核者 
@PubDate  拟制时间 
@CheckDate  审核时间 
@PassDate  批准时间 
@State  表单状态 
@FormType  表单类型 
@SupplierCode  供应商代码 
@CompanyType  厂别 
@InputModelMaterial  输入成本模型的物料 
@Purchaser   
@Auditor   
@Checker   
@RejectReason   
*/    
Create PROCEDURE UBS_CAS_PriceFormBatchAction
    @DataAction int,
    @ID int = 0,
    @BatchNo varchar(50),
    @ItemClassID int,
    @PurchaserUID int,
    @AuditorUID int,
    @CheckerUID int,
    @PubDate datetime,
    @CheckDate datetime,
    @PassDate datetime,
    @State int,
    @FormType int,
    @SupplierCode varchar(50),
    @CompanyType int,
    @InputModelMaterial varchar(50),
    @Purchaser varchar(50),
    @Auditor varchar(50),
    @Checker varchar(50),
    @RejectReason varchar(500)
AS
begin   tran 
   SET NOCOUNT ON

 if @DataAction=0
begin
    
    insert into UBS_CAS_PriceFormBatch
    (
        [BatchNo],
        [ItemClassID],
        [PurchaserUID],
        [AuditorUID],
        [CheckerUID],
        [PubDate],
        [CheckDate],
        [PassDate],
        [State],
        [FormType],
        [SupplierCode],
        [CompanyType],
        [InputModelMaterial],
        [Purchaser],
        [Auditor],
        [Checker],
        [RejectReason]
    ) 
    values
    (
        @BatchNo,
        @ItemClassID,
        @PurchaserUID,
        @AuditorUID,
        @CheckerUID,
        @PubDate,
        @CheckDate,
        @PassDate,
        @State,
        @FormType,
        @SupplierCode,
        @CompanyType,
        @InputModelMaterial,
        @Purchaser,
        @Auditor,
        @Checker,
        @RejectReason
    )
    set 
        @ID=scope_identity()
end
if @DataAction=1
begin
    Update [UBS_CAS_PriceFormBatch] SET
        [BatchNo] = @BatchNo,
        [ItemClassID] = @ItemClassID,
        [PurchaserUID] = @PurchaserUID,
        [AuditorUID] = @AuditorUID,
        [CheckerUID] = @CheckerUID,
        [PubDate] = @PubDate,
        [CheckDate] = @CheckDate,
        [PassDate] = @PassDate,
        [State] = @State,
        [FormType] = @FormType,
        [SupplierCode] = @SupplierCode,
        [CompanyType] = @CompanyType,
        [InputModelMaterial] = @InputModelMaterial,
        [Purchaser] = @Purchaser,
        [Auditor] = @Auditor,
        [Checker] = @Checker,
        [RejectReason] = @RejectReason
    Where
        
        [ID] = @ID
end
if @DataAction=2
begin
    delete from [UBS_CAS_PriceFormBatch] where  [ID] = @ID
end


if @DataAction=3
begin
select 
    [BatchNo],
    [ItemClassID],
    [PurchaserUID],
    [AuditorUID],
    [CheckerUID],
    [PubDate],
    [CheckDate],
    [PassDate],
    [State],
    [FormType],
    [SupplierCode],
    [CompanyType],
    [InputModelMaterial],
    [Purchaser],
    [Auditor],
    [Checker],
    [RejectReason],
    from [UBS_CAS_PriceFormBatch]    
    where
        
        [ID] = @ID
        
        
        end
        
        
        if   @@error<>0   goto   sqlerr 

commit   tran   
  return   
  sqlerr:   
      rollback   

    SET NOCOUNT OFF
GO

转载于:https://www.cnblogs.com/eastjazz/archive/2008/05/28/1209482.html