主页 / dba / 问题 / 29755
Accepted
Royi Namir
Asked: 2012-12-05 00:03:01 +0800 CST

插入和更新的单独存储过程？

  • 772

我在 Microsoft SQL Server 中有一个表。有时我需要更新，有时我需要插入。我可以写2个存储过程：

InsertNewPerson
UpdatePertsonById

但我正在考虑编写 1 个存储过程，而不是 ( SetPerson)，这两者都可以（如果有ID，它是一个update操作，否则insert）。

我应该创建一个存储过程（只维护一个）还是应该创建两个不同的存储过程？

sql-server sql-server-2008

2 个回答

  1. Best Answer

    据我了解，您实际上并不是在谈论UPSERT这里只是在一个存储过程中组合两个不同的 CRUD 操作。

    CREATE PROC InsertOrUpdateYourTable @Id int = NULL OUTPUT,
                                    @Foo INT,
                                    @Bar VARCHAR(10)
AS
    IF @Id IS NULL
      BEGIN
          INSERT INTO YourTable
                      (Foo,
                       Bar)
          VALUES      (@Foo,
                       @Bar)

          SET @Id = SCOPE_IDENTITY()
      END
    ELSE
      BEGIN
          UPDATE YourTable
          SET    Foo = @Foo,
                 Bar = @Bar
          WHERE  Id = @Id
      END

    我看到的好处是，如果表结构发生变化，您不必维护两个单独的参数列表。缺点是单个存储过程现在有两个职责，不太容易理解。

    我通常会选择将它们分成两个存储过程。

    RE：“你能详细说明一下 upsert 的外观吗？”

    CREATE PROC UpsertYourTable
@Id int,
@Foo int,
@Bar varchar(10)
AS
MERGE YourTable WITH (HOLDLOCK)  AS T
        USING ( VALUES ( @Id, @Foo, @Bar ) ) 
              AS source ( Id, Foo, Bar)
        ON ( T.Id = source.Id )
        WHEN MATCHED 
            THEN 
        UPDATE SET
                Foo = source.Foo ,
                Bar = source.Bar
        WHEN NOT MATCHED 
            THEN    
        INSERT  (Id, Foo , Bar)
               VALUES 
               (@Id, @Foo , @Bar);

    这假定它Id不再是一IDENTITY列。HOLDLOCK 这里解释了使用的原因。

    • 9

  2. 如果您确实想使用一个过程，您可以使用MERGE 语句。

    示例合并代码：

    create table testo(Id int identity(1,1) NOT NULL, somechar char(1), someint int, AddedTime datetime2(0), LastModifiedTime datetime2(0));
alter table testo add constraint [PK_testo] primary key(Id);    --Clustered index on target table.
--No index necessary on the source 'table' because we're creating it from parameters.

declare @Id int = 1;
declare @somechar char(1) = 'A';
declare @someint int = 42;

declare @results table (DMLAction sysname, Id int, somechar char(1), someint int );

MERGE dbo.testo AS Target
    USING
        (
            SELECT  
                @Id as Id,
                @somechar as somechar,
                @someint as someint
        ) AS SOURCE
    ON
        TARGET.Id = SOURCE.Id
    WHEN MATCHED
        THEN UPDATE SET 
            TARGET.somechar = SOURCE.somechar,
            TARGET.someint = SOURCE.someint,
            TARGET.LastModifiedTime = CURRENT_TIMESTAMP
    WHEN NOT MATCHED BY TARGET
        THEN INSERT
        (
            somechar,
            someint,
            AddedTime,
            LastModifiedTime
        )
        VALUES
        (
            SOURCE.somechar,
            SOURCE.someint,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
        )
    OUTPUT
        $action,
        inserted.Id,
        inserted.somechar,
        inserted.someint
    INTO
        @results;

select * from @results;

    最大的缺点是性能会更差（尽管您可以通过明智的索引来缓解性能问题）。抵消这一点，有一些优势。

    第一个优点是审计更加完整，因为您可以在需要时访问 SOURCE 和 TARGET 记录值。这是您无法从 INSERT 或 UPDATE 的 OUTPUT 子句中得到的东西。

    这种方法的第二个优点是您可以在数据层中创建一个表值参数，而不是使用单个参数（就像我在这个快速示例中所做的那样）并将其用作 SOURCE 表。这意味着理论上您可以在同一个调用中插入/更新多条记录。当然，您可以在同一过程或两个单独的存储过程中对单独的 INSERT 和 UPDATE 语句执行相同的操作，但这使您可以维护一个 T-SQL 语句而不是两个。

    最后，如果您想付出必要的努力，您可以获取 Merge 的 OUTPUT 子句（上面示例中的@results）的结果，将其反馈给 .NET（或任何您的应用程序层）并刷新您的对象表结果。这不是一个简单的练习，但它可以为您节省另一个 Get 调用。如果同时插入多条记录，则不必担心尝试通过 SCOPE_IDENTITY() 获取标识列结果：插入的伪表已经具有每条记录的标识列值。

    同样，大多数优点是您可以使用单独的插入和更新语句复制的东西，因此这主要归结为 MERGE 的性能成本与必须在一个位置而不是两个位置正确获取它的维护收益，并且不需要单独在您的业务或数据层中插入/更新逻辑。

    • 3

相关问题