Mysql insert stored procedures

David Smith

I want to know which is the best route to take when verifying if record exists before entering in another record in the database or table.

Do I let the stored procedure handle whether or not record exists or do I handle in code?

Which is the most effective way to this?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

David Smith14y ago
What if I have to verify three fields at once within a stored procedure, how would you check to see if they exists or not . Keep in mind all three fields are primary keys. Can you provide an example or syntax please?
Arshad14y ago
Hi,

you can write some thing like below inside the proc.

IF NOT EXISTS(Select ColumnName from table Where ColumnName==@InputParameter)
        BEGIN
         --INSERT LOGIC HERE
        END

Regards,
Arshad
David Smith14y ago
Can you show me how to check if a record exist or not in the stored procedure. The logic i want is to verify verify if record exists, if exists return record exists, else insert record. Can you help me below. I know how to do it in code. But I dont know how to do it in a stored procedure.

prc_AddIIcca
(

    IN    Color VARCHAR (50),
    IN    Food VARCHAR (50)
)
BEGIN

INSERT INTO iicca
    (
       Color,
   Food
    )
VALUES
    (
       Color,
       Food
    ) ;
END
Arshad14y ago
Hi Darnell,

If you going to check at the code level, then you might have to hit the database twice to insert a record. but if you keep that logic inside the proc then it will be a single hit and easy. so as per me you can keep the EXISTS logic inside the proc only.

Regards,
Arshad