使用TRY / CATCH执行INSERT / UPDATE [英] Using TRY / CATCH to perform INSERT / UPDATE

问题描述

我有一些存储过程中的这种模式

   -  Table1 
 [id] [int] IDENTITY（1,1）NOT NULL 
 [data] [varchar]（512）NULL 
 [count] INT NULL 
 
  - 'data'是唯一的table1中的'data'的索引
 BEGIN TRY 
 INSERT INTO表1（数据，计数）SELECT @ data，1; 
 END TRY 
 BEGIN CATCH 
更新表1 SET count = count + 1 WHERE data = @data; 
 END CATCH 
  

我已经被击中了使用此模式

$ b $的.com / questions / 30710135 / abstracting-out-a-query-to-a-stored-procedure-make-it-run-very-slow b

在正常的逻辑流程中，您不应该有异常捕获。 （因此为什么它被称为异常..它应该是异常的（罕见的）。对你的INSERT进行一个存在检查。如果不存在（从数据中选择null from data = @data）begin / * insert here * / END

但是，在这种情况下，我看不到方法，请考虑以下替代方法。 b
$ b

  INSERT INTO Table1（data，count）
 SELECT @ data，1 WHERE NOT EXISTS 
（SELECT 1 FROM Table1 WHERE data = @data）
  

如果我这样做，这意味着每个插入都是唯一的，但我不能'catch'一个更新条件。

  DECLARE @id INT; 
 SET @id =（SELECT id FROM TABLE1 WHERE data = @data）
 
 IF（@id IS NULL）
 INSERT INTO Table1（data，count）SELECT @ data，1; 
 ELSE 
 UPDATE Table1 SET count = count + 1 WHERE data = @data; 
  

如果我这样做，我有一个竞争条件在支票和插入之间，所以我可以h ave重复插入。

  BEGIN TRANSACTION 
 DECLARE @id INT; 
 SET @id =（SELECT id FROM Table1 WHERE data = @data）
 
 IF（@id IS NULL）
 INSERT INTO Table1（data，count）SELECT @data， 1; 
 ELSE 
更新表1 SET count = count + 1 WHERE data = @data; 
 END TRANSACTION 
  

如果我把它包含在一个 TRANSACTION 它增加了更多的开销。我知道 TRY / CATCH 也带来开销，但我认为 TRANSACTION 添加更多 - 任何人都知道？。人们不断告诉我，在正常的应用程序逻辑中使用 TRY / CATCH 是 ，但不会告诉我为什么

注意：我至少在一个框上运行SQL Server 2005，所以我不能使用 MERGE

解决方案

尝试更新如果失败 - 插入新的。

  BEGIN TRANSACTION 
更新t 
 SET 
 t.count = t.count + 1 
 FROM Table1 t 
 WHERE t.data = @data 
 
 IF（@@ ROWCOUNT = 0）
 BEGIN 
 INSERT INTO Table1 
（data，count）
 VALUES 
（@data，1）
 END 
 COMMIT TRANSACTION 
  

I have this pattern in a number of stored procedures

-- Table1
[id] [int] IDENTITY(1,1) NOT NULL
[data] [varchar](512) NULL
[count] INT NULL

-- 'data' is unique, with a unique index on 'data' in 'Table1'
BEGIN TRY 
    INSERT INTO Table1 (data, count) SELECT @data,1;
END TRY
BEGIN CATCH
    UPDATE Table1 SET count = count + 1 WHERE data = @data;
END CATCH

I've been slammed before for using this pattern

You should never have exception "catching" in your normal logic flow. (Thus why it is called an "exception"..it should be exceptional (rare). Put a exists check around your INSERT. "if not exists (select null from Data where data = @data) begin /* insert here */ END

However, I can't see a way around it in this instance. Consider the following alternative approaches.

INSERT INTO Table1 (data,count) 
SELECT @data,1 WHERE NOT EXISTS 
    (SELECT 1 FROM Table1 WHERE data = @data)

If I do this, it means every insert is unique, but I can't 'catch' an update condition.

DECLARE @id INT;  
SET @id = (SELECT id FROM Table1 WHERE data = @data)

IF(@id IS NULL)
    INSERT INTO Table1 (data, count) SELECT @data,1;
ELSE 
    UPDATE Table1 SET count = count + 1 WHERE data = @data;

If I do this, I have a race condition between the check and the insert, so I could have duplicates inserted.

BEGIN TRANSACTION
   DECLARE @id INT;  
   SET @id = (SELECT id FROM Table1 WHERE data = @data)

   IF(@id IS NULL)
       INSERT INTO Table1 (data, count) SELECT @data,1;
   ELSE 
       UPDATE Table1 SET count = count + 1 WHERE data = @data;
END TRANSACTION

If I wrap this in a TRANSACTION it adds more overhead. I know TRY/CATCH also brings overhead but I think TRANSACTION adds more - anyone know?.

People keep telling me that using TRY/CATCH in normal app logic is BAD, but won't tell me why

Note: I'm running SQL Server 2005 on at least one box, so I can't use MERGE

解决方案

Try to update and if it's failed - to insert new.

BEGIN TRANSACTION
    UPDATE t
    SET
            t.count = t.count + 1
    FROM Table1 t
    WHERE t.data = @data

    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Table1
        (data, count)
        VALUES
        (@data, 1)
    END
COMMIT TRANSACTION

这篇关于使用TRY / CATCH执行INSERT / UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！