Error handling in SQL Server stored procedure

Question

I have a T-SQL query in a stored procedure which inserts data into a table using SELECT command from another table. Problem is it stops query execution if it found duplicate entry in select table clause while inserting it in another table (I have imposed primarky key constraint on this)

I want SQL to skip the error occurred (i.e. don't throw it nd stop.. continue your execution) and go with next line of row inserting..

I know there are ways with TRANSACTION COMMIT ROLLBACK TRY CATCH but how to use them ?

My T-SQL :

Begin

    Set @SQL='Insert Into AxisReports
                Select *
                From ReportData L 
                Left Join ATM A On L.ATMID=A.ATM 
                Where L.ATMID=A.ATM AND L.IssuerNetwork < > ''0000'' '
    Exec(@SQL)

End

The source table may contain more than 5 Lac entries with very small no. of duplicate rows.

Why are you using dynamic SQL for this? Does the table already have rows or are the dupes being brought back by the SELECT itself? — Martin Smith
– Martin Smith, Commented Sep 15, 2012 at 8:43
Duplicate Entry Present only in Select Table and I want to insert only One entry of the two or more duplicate entries found in Select table. — user1194919
– user1194919, Commented Sep 15, 2012 at 9:00
You should alter the SELECT to not bring back duplicates then. This will also allow you to select which duplicate row to use. As you haven't told us the table structures or the key columns for the unique index I can't give you any code though. — Martin Smith
– Martin Smith, Commented Sep 15, 2012 at 9:02
Duplicate Table contains with Duplicate rows with ALL 36 Columns as Same data on both rows...How can i avoid that in SELECT ? and Insert Only ONE ROW ?????? — user1194919
– user1194919, Commented Sep 15, 2012 at 11:11

Saurabh R S · Accepted Answer · 2012-09-15 13:36:16Z

1

Instead of interfering with the Keys of the tables I would suggest you to redesign your code so that duplicate rows shouldn't be allowed to go into your destination table at the first place.
You may change your INSERT SQL to something like this:

INSERT INTO AxisReports
SELECT x.ATMID FROM
    (SELECT * 
       FROM ReportData L 
  LEFT JOIN ATM A ON L.ATMID=A.ATM 
      WHERE L.ATMID=A.ATM) x
LEFT OUTER JOIN AxisReports y ON x.ATMID =  y.RepID
WHERE y.RepId IS NULL

Hope it helps.

answered Sep 15, 2012 at 13:36

Saurabh R S

3,1871 gold badge38 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff S · Accepted Answer · 2012-09-15 13:05:33Z

0

Try using a Distinct clause in your query:

Begin

    Set @SQL='Insert Into AxisReports
                Select distinct *
                From ReportData L 
                Left Join ATM A On L.ATMID=A.ATM 
                Where L.ATMID=A.ATM AND L.IssuerNetwork < > ''0000'' '
    Exec(@SQL)

End

Note that this will only work if the values for all of the columns returned from the query are the same for the duplicate rows.

answered Sep 15, 2012 at 13:05

Jeff S

7,49432 silver badges32 bronze badges

2 Comments

user1194919 Over a year ago

You have any Idea on How much time it will take to execute this query if it has More than 3 Lac Records with just 10 Duplicate rows ???

Saurabh R S Over a year ago

Try and check it. Anyways 3 Lac (0.3 Million) is not at all a huge number if your code is optimized and indexes are in place.

Collectives™ on Stack Overflow

Error handling in SQL Server stored procedure

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related