一个存储过程实现对表 UBS_CAS_PriceFormBatch 进行添加、更新、删除、获取操作。
--存储过程的功能:对表 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 |
最新评论