[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Ignore Error in a store procedure and continue

Posted on 2010-08-20
5
Medium Priority
?
316 Views
Last Modified: 2012-05-10
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
Comment
Question by:tvae
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:Aamir Sarfaraz
ID: 33488471
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
 

Author Comment

by:tvae
ID: 33488642
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
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 1000 total points
ID: 33488656
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
 
LVL 2

Expert Comment

by:Aamir Sarfaraz
ID: 33488658
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33488672
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question