What is the proper way to handle this error in my stored procedure?

Question

From PHP, I am calling a SQL stored procedure. The stored procedure checks the database, and either inserts the data if it's new, or modifies data that already exists.

I'm getting an error that one of my parameters is of an incorrect data type. I can't really control this because of where the data is coming from. I am calling the stored procedure hundreds, or thousands of times looping through an array of data, and I only get this error for a couple of records out of the batch.

What is the proper way to handle this error in the stored procedure? If any of the parameters have the incorrect data type, I just want to skip that record and move on to the next record. Each call comes from a foreach loop.

The database is reporting the error and PHP is displaying it. I do not have experience with error handling.

Example PHP Code:

foreach($item_array as $item) {
    $id = $item['id'];
    $color = $item['color'];

    $con = connect()
    $query = 'EXECUTE PROCEDURE sp_update_db(:id, :color);'
    $params = array(':id' => $id, ':color' => $color);
    $stmt = prepare($con, $query);

    $result = execute($stmt, $params);

    close($con);
}

Running the code I get "Warning: SQL error: [stored procedure a paramater was of the incorrect datatype]".

score 0 · Accepted Answer · edited May 23 '17 at 11:47

Looks to me that your proc is not even executed as a result of the wrong parameter being passed; you can only handle this on your php code by catching the exception.

If the proc is in fact being called, but simply failling inside the procedure due to some sort of data type mismatch, you can use

BEGIN TRY
    -- your proc statements here
END TRY
BEGIN CATCH
END CATCH

Documentation here.

UPDATE

Since you said that you are calling the proc once per each record that you need to process, you need to catch the error on the PHP side. You can use try/catch blocks on PHP. See here.

Basically, you'd need to have the try/catch block inside your foreach loop enclosing only the part that calls the stored procedure; however, I would just have an if statement before the stored procedure call that makes sure all the parameters that will be passed to the proc are of the expected type and lenght. For example, if stored procedure expectes parameter @a of type int, I would prevent the proc from being called at all if the parameter that is about to be passed is not a number or an empty string.

UPDATE 2

Based on sample php (Warning: I am not a PHP coder), seems like this will work:

try{
    $result = execute($stmt, $params);
}
catch (Exception $e) {
}

But again, if you know the data types expected by the proc, why not have an if instead of the try/catch?

The database is reporting the error and PHP is displaying it. — MikeJerome, Nov 11 '11 at 03:14
@MikeJerome so your issue is that you don't know how to continue looping through the remaining items when an error occurs? — Icarus, Nov 11 '11 at 03:17
I have never handled errors before at all, so I don't know whether I catch it at the stored procedure, catch it in my foreach loop or if I have to catch it in the stored procedure and return something to PHP or what. — MikeJerome, Nov 11 '11 at 03:20
I'm thinking I should use Try Catch in the stored proc, but do I need to do anything else? — MikeJerome, Nov 11 '11 at 03:21

What is the proper way to handle this error in my stored procedure?

1 Answers1