How to catch and handle a duplicate error while inserting data through a stored procedure

Question

I have a stored procedure to insert data into a table.

This is how I call the stored procedure from the controller method:

var insert_query = entities.Database.SqlQuery<Call_Info>("exec [dbo].[insert_call_info] @call_id, @user_id, @call_arrive, @call_end, @info",
                    new SqlParameter("call_id", call_id),
                    new SqlParameter("user_id", u_id),
                    new SqlParameter("call_arrive", call_arrive),
                    new SqlParameter("call_end", call_end),
                    new SqlParameter("info", info)
                    ).ToList();

 var jsonResult = JsonConvert.SerializeObject(insert_query); // <-- using Newtonsoft.Json
 return Json(jsonResult);

If I insert a call_id value that is already stored in the table, I get this error, which is correct:

Message": "An error has occurred

Violation of UNIQUE KEY constraint 'UQ__Call_Inf__427DCE6904E3DF9B'. Cannot insert duplicate key in object 'dbo.Call_Info'. The duplicate key value is (91390).

Is there a way to catch this error in the controller?

Modify your stored procedure to avoid such errors completely. Add a WHERE clause in your INSERT statement for example, or replace INSERT into a MERGE that updates any existing records — Panagiotis Kanavos
@PanagiotisKanavos thank you! I will use this for an update stored-procedure. — zinon

mb14 mb14 · Accepted Answer · 2020-05-25T12:56:16

Definitely. You can use a try catch block to capture the SqlException

try
{
    //SQL query
}
catch(SqlException ex)
{
    foreach(SqlError err in ex.Errors)
    {
        if((err.Number == 2601) //unique nonclustered index
            || (err.Number == 2627)) //unique constraint
        //handle it
    }
}

How to catch and handle a duplicate error while inserting data through a stored procedure

2 Answers