SQL Server - 存储过程 - 忽略异常 [英] SQL server - stored procedure - ignore exception

问题描述

我有一个存储过程:

INSERT INTO SitesCategories(SiteID, CategoryID) VALUES(@SITEID, @TempCId);

这个插入可能会抛出异常，因为我在表上有这个约束:

This insert could throw exceptions because I have this constraint on the table:

  ALTER TABLE dbo.SitesCategories
    ADD CONSTRAINT UniqueSiteCategPair
    UNIQUE (SiteId,CategoryID);

我做了这个约束，这样我就不必在插入对的唯一性(@SITEID、@TempCId)时进行检查.但是我不希望在存储过程中执行此操作时引发 SQl 异常.如何捕获"存储过程中的异常并在过程中继续操作?

I made this constraint so that I would not have to check when inserting the uniques of the pairs (@SITEID, @TempCId). But I do not want that an SQl exception is thrown when this gets executed inside the stored procedure. How can I "catch" the exception inside the stored procedure and continue operations inside the procedure ?

推荐答案

如果您使用 IGNORE_DUP_KEY = ON 设置创建您的 UNIQUE CONSTRAINT，那么重复项将被静默忽略，例如它们不会被插入到您的表中，但也不会抛出异常:

If you create your UNIQUE CONSTRAINT with the IGNORE_DUP_KEY = ON setting, then duplicates will be silently ignored, e.g. they won't be inserted into your table, but no exception will be thrown, either:

 ALTER TABLE dbo.SitesCategories
   ADD CONSTRAINT UniqueSiteCategPair
   UNIQUE (SiteId,CategoryID) WITH (IGNORE_DUP_KEY = ON);

这篇关于SQL Server - 存储过程 - 忽略异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！