SQL Server 表值参数和Merge语句配合更新明细数据示例

liudongbei

应用场景：
批量更新数据库中表记录，同时进行增、删、改操作，如：单据明细提交。

示例SQL：
1.创建表值参数：
CREATE TYPE [Common].[ValueRange] AS TABLE(
[ValueID] [int] NULL,
[ValueCode] [dbo].[StandardCode] NOT NULL,
[ValueText] [nvarchar](64) NOT NULL,
[PYCode] [varchar](20) NULL,
[WBCode] [varchar](20) NULL,
[CategoryCode] [varchar](40) NOT NULL,
[isDisabled] [bit] NOT NULL,
[Remark] [nvarchar](64) NULL
)
GO

2.创建存储过程：
--该存储过程同时保存主表和明细表所有数据
create procedure [Common].[up_saveValueRangeWithCategory]
@categoryID int,
@categoryCode varchar(40),
@categoryName nvarchar(32),
@pyCode varchar(20),
@wbCode varchar(20),
@isStandard bit,
@isDisabled bit,
@remark nvarchar(64),
@valueRange Common.ValueRange readonly  --表值参数，.net中可使用Datatable，一次性保存所有明细数据
as
begin
if exists(select 1 from Common.ValueRangeCategory where CategoryCode = @categoryCode)
begin
  --更新值域分类
  update Common.ValueRangeCategory set CategoryCode = @categoryCode, CategoryName = @categoryName, PYCode = @pyCode, WBCode = @wbCode, isStandard = @isStandard, isDisabled = @isDisabled, Remark = @remark
  where CategoryID = @categoryID;
end else
begin
  insert into Common.ValueRangeCategory(CategoryCode, CategoryName, PYCode, WBCode, isStandard, isDisabled, Remark)
  values(@categoryCode, @categoryName, @pyCode, @wbCode, @isStandard, @isDisabled, @remark);
end;

--更新值域表  merge语句同时实现增删改
merge Common.ValueRange as t
using @valueRange as s
on s.ValueID = t.ValueID
when matched then  --存在则更新
  update set ValueCode = s.ValueCode, ValueText = s.ValueText, PYCode = s.PYCode, WBCode = s.WBCode, isDisabled = s.isDisabled, Remark = s.Remark
when not matched then  --不存在插入
  insert (ValueCode, ValueText, PYCode, WBCode, CategoryCode, isDisabled, Remark)
  values(s.ValueCode, s.ValueText, s.PYCode, s.WBCode, s.CategoryCode, s.isDisabled, s.Remark)
when not matched by source and t.CategoryCode = @categoryCode then  --标值参数数据中不存在则删除
  delete ;
end

GO

是不是很方便。

羽叶

有软妹币拿

liudongbei

天堂羽叶 发表于 2013-6-28 17:32
有软妹币拿

拿了也没什么用哦