一個存儲過程實現增刪操作
--存儲過程的功能：對表 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
上文中的內容主要是以代碼的形式展現出來的，對于剛入門的初學者可能比較難理解，希望大家都能很好的掌握這些知識，為大家以后工作帶來方便。
【編輯推薦】
