添加的存储过程

最新推荐文章于 2022-05-15 10:49:49 发布

原创最新推荐文章于 2022-05-15 10:49:49 发布 · 768 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储 #manager

学习专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个SQL事务处理的示例，展示了如何检查记录是否存在以决定进行更新还是插入操作，并通过存储过程实现了这一逻辑。此外，还提供了一个具体的存储过程示例，用于分销办公室信息的维护。

  Begin transaction --开始事务
if exists(select 1 from [BMDS_CustomerKindPlan] where [Year] = @Year and [Month] = @Month and [CustomerCode] = @CustomerCode)
begin
  raiserror('添加失败,该记录已存在',16,1)
  commit Transaction
  return
end
  INSERT INTO [BMDS_CustomerKindPlan](
  [PlanCode],[Year],[Month],[CustomerCode],[CreatedBy]
  )VALUES(
  @PlanCode,@Year,@Month,@CustomerCode,@CreatedBy
  )
IF @@ERROR!=0
  BEGIN
   Rollback Transaction
   select '0' as ifCanDo
  END
Else
  BEGIN
   commit Transaction
   select @@identity as ifCanDo
  END

存储过程

如果存在就修改，不存在就添加

if exists (select 1 from [BTS_ERP_DistributionOffice] WHERE [OfficeCode]=@OfficeCode)
  UPDATE [BTS_ERP_DistributionOffice] SET
  [OfficeName] = @OfficeName,[OfficeNameB] = @OfficeNameB,[CompanyType] = @CompanyType,[OilCategoryToSell] = @OilCategoryToSell,[OraganiseCode] = @OraganiseCode,[Manager] = @Manager,[Orderindex] = @Orderindex,[ModifiedDate] = GETDATE ()
  WHERE OfficeCode=@OfficeCode
else
  INSERT INTO [BTS_ERP_DistributionOffice](
  [OfficeCode],[OfficeName],[OfficeNameB],[CompanyType],[OilCategoryToSell],[OraganiseCode],[Manager],[Orderindex]
  )VALUES(
  @OfficeCode,@OfficeName,@OfficeNameB,@CompanyType,@OilCategoryToSell,@OraganiseCode,@Manager,@Orderindex
  )