• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Ignore Error in a store procedure and continue

I have a stored procedure that insert to a table and I have creadted a primary key. I want to ignore the error Violation of Primary Key - cannot isert duplicate key.

If the record already exist then just continue with the rest of the procedure.
0
tvae
Asked:
tvae
  • 2
  • 2
1 Solution
 
Aamir SarfarazIT ManagerCommented:
you can handel the errors like this,


DECLARE @Error INT

-- DO Stuff
UPDATE client SET cliTypCd = (SELECT cliTypCd FROM client T WHERE T.link = @throwLink) WHERE link = @keepLink
SET @Error = @@ERROR

IF @Error <> 0 AND @Error <> 30002
BEGIN
      -- Handle Error
END
-- Else Ignore
0
 
tvaeAuthor Commented:
aamsoh,

What is this step doing?
UPDATE client SET cliTypCd = (SELECT cliTypCd FROM client T WHERE T.link = @throwLink) WHERE link = @keepLink

Do i need to put replace anything in there?
0
 
Rajkumar GsSoftware EngineerCommented:
Please check the attached script

Raj
-- Case #1
-- Copying bulk data from Table1 to Table2
INSERT INTO Table2
	SELECT * FROM Table1
	WHERE ID NOT IN (SELECT ID FROM Table2)
	-- AND (if any where conditions add here)

-- Case #2
-- Copying single row at a time
IF (SELECT COUNT(*) FROM Table2 WHERE ID = @ID) = 0
BEGIN
	INSERT INTO Table2 SELECT * FROM Table1 
						-- WHERE ... (any where condition add here)
END

Open in new window

0
 
Aamir SarfarazIT ManagerCommented:
actully this was only a sample. you should consider on this code:
you can use this error handel with INSERT OR UPDATE quety.


SET @Error = @@ERROR

IF @Error <> 0 AND @Error <> 30002
BEGIN
      -- Handle Error
END
-- Else Ignore
0
 
Rajkumar GsSoftware EngineerCommented:
If you are inserting bulk records, first script can help. It will insert only new records based on primary key.

If you are inserting single record at a time, then you can check whether any row with the primary key value you are going to insert, already exists in the table. If not insert.

Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now