• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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