Avoid entering duplicate records through procedure

Hi
All

I want to insert the record into a table after checking that the record is not available in the table.

below is the code:-

CREATE PROCEDURE newprcSaveGPSLocation(
_lat VARCHAR(45),
_lng VARCHAR(45),
_mph VARCHAR(45),
_direction VARCHAR(45),
_distance VARCHAR(45),
_date VARCHAR(100),
_locationMethod VARCHAR(100),
_phoneNumber VARCHAR(20),
_sessionID VARCHAR(50),
_accuracy VARCHAR(20),
_locationIsValid VARCHAR(5),
_extraInfo VARCHAR(255),
_recid VARCHAR(25)
)
BEGIN
DECLARE l_count INTEGER;
select count(*) into l_count from locations where record_id =‘_recid’;

IF l_count<=0 THEN
INSERT INTO locations (latitude, longitude, speed, direction, distance, gpsTime, locationMethod,phoneNumber,sessionID, accuracy,isLocationValid,extraInfo,record_id)
VALUES (_lat,_lng,_mph,_direction,_distance,_date,_locationMethod,_phoneNumber,_sessionID,_accuracy,_locationIsValid,_extraInfo,_recid);
END IF;
END

But this is not working as expected. If already record is there with same record id it should not insert the
data into table. But it is inserting

please help me out to resolve this issue

Thanks
MD.Samiuddin

doing a SELECT in order to prevent a subsequent INSERT from creating a duplicate is not the “best practice” approach

you should declare a UNIQUE constraint on the column instead

then just go ahead an issue an INSERT (without doing the SELECT first), and simply check the database status code afterwards, to see if a duplicate was rejected