CODEWITHSUNDEEP
sql-serverstored-proceduresexception
harryjohn
harryjohn

Reputation: 686

Handling exceptions in stored procedure: ignore errors and continue during inserts

I have a stored procedure which accepts a Table Type and inserts the value into a table. I'm calling this stored procedure by passing 1000 books in the parameter. But I want to handle exceptions such that, even if one or two rows in the parameter fail insertion, other rows should get inserted. How do I do this?

CREATE PROCEDURE InsertBooks
    @newBooks BooksTable READONLY
AS
    INSERT INTO book 
        SELECT @newBooks

Thanks in advance.

Upvotes: 0

Views: 1627

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

If you simply want duplicates (as per the UNIQUE Index / Constraint) ignored and not tracked separately, then that can be done rather easily by enabling the IGNORE_DUP_KEY option of the Unique Index or Unique Constraint, as shown in the following example:

IF (OBJECT_ID(N'tempdb..#tmp') IS NOT NULL)
BEGIN
  DROP TABLE #tmp;
END;
CREATE TABLE #tmp
(
  TmpID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
  Code  VARCHAR(10) NOT NULL UNIQUE WITH (IGNORE_DUP_KEY = ON),
  OtherValue INT NOT NULL
);

INSERT INTO #tmp (Code, OtherValue)
  SELECT tab.ColA, tab.ColB
  FROM   (
          VALUES ('A', 1), ('B', 1), ('C', 1), ('B', 2),
                 ('B', 3), ('C', 2), ('D', 1), ('B', 4)
         ) tab(ColA, ColB)
  --ORDER BY tab.ColB DESC;

SELECT *
FROM   #tmp;

Returns:

-- no ORDER BY (i.e. ORDER BY is commented out):

TmpID   Code   OtherValue
1       A      1
2       B      1
6       C      2
7       D      1


-- using the ORDER BY:

TmpID   Code   OtherValue
4       C      2
6       D      1
7       B      1
8       A      1

Please be aware:

  • As shown in the example output above, when there are duplicates, you cannot fully control which item is inserted first and which ones are considered the duplicates.

  • There is a performance hit for enabling this option. Please see the following blog post for additional details: Maintaining Unique Indexes with IGNORE_DUP_KEY

Upvotes: 0

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

You accomplish this by setting up your insert statement to prevent individual rows from causing errors. There are two reasons a single row might fail to insert properly from your table parameter into the actual table:

  1. Schema mismatch. You prevent this by ensuring the schema of the table parameter exactly match those of the target table.

  2. Constraint violations. Prevent this by running a filter query against the input, so bad rows get removed from the data prior to insertion. The most likely version of this would be invalid keys, but the same rule applies regardless of the constraint type.

For example, let's say your book table has a foreign key to authors, authorID. You'd filter the input like so:

SELECT nb.* 
FROM @newBooks nb 
INNER JOIN authors a
ON nb.authorID = a.authorID

Then you could insert the result of this query into books without worrying that there might be invalid authors in the new data.

One additional note: if you're trying to insert specific primary key values, you must filter out duplicates and also SET IDENTITY_INSERT book ON before inserting any data. Don't forget to turn IDENTITY_INSERT back off after you're done.

Upvotes: 1

Related Questions