delete using stored proc in mysql drops entire table

Question

I have a stored proc which has only 1 line in it to delete a record in a table given a condition. When I call the storedproc using 'CALL storedprocname('2');', it deletes all records from the table i.e. truncates. I have run the delete statement independently and it works fine, deleting only specified record. Am I missing something? (I am using mysqlworkbench on macosx)

DROP PROCEDURE IF EXISTS usp_DeleteCompany;
DELIMITER $$
CREATE PROCEDURE usp_DeleteCompany (IN CompanyId VARCHAR(3))
BEGIN
DELETE FROM Company WHERE CompanyId = (CAST(CompanyId AS UNSIGNED));
-- DELETE FROM Company WHERE CompanyId = (CAST('2' AS UNSIGNED))
END; $$

Answer 1

Why not pass the parameter as unsigned directly like IN CompanyId UNSIGNED? no need of casting it then.

Also, consider changing the parameter name to something different than the actual column name CompanyId.

To me it seems like your delete query below

DELETE FROM Company WHERE CompanyId = (CAST(CompanyId AS UNSIGNED));

getting converted to below cause it's considering CompanyId as the exact column of the table instead of the actual parameter passed to the procedureand so end up deleting all rows.

DELETE FROM Company WHERE CompanyId = CompanyId;

Consider using a different name for the parameter like Cid in your query

CREATE PROCEDURE usp_DeleteCompany (IN Cid UNSIGNED)
BEGIN
DELETE FROM Company WHERE CompanyId = Cid;
END;