CODEWITHSUNDEEP
mysqlsqlstored-proceduresprepared-statement
Snipchain
Snipchain

Reputation: 147

Unknown column in 'field list in stored procedure

I'm creating a stored procedure but facing a problem with a field name... when i run the procedure i have an error Unknown column 'pa' in 'field list but i don't understand why...

CREATE PROCEDURE `deletePost`(IN p_id_post INT, IN p_Tbl_ref CHAR(2))
BEGIN

SET @id_post = p_id_post; 
SET @Tbl_ref = p_Tbl_ref;

DELETE FROM `tbl` WHERE tbl_ref_product = @Tbl_ref AND id_table = @id_post;

END

I have also tried prepared statement...

CREATE PROCEDURE `deletePost`(IN p_id_post INT, IN p_Tbl_ref CHAR(2))
BEGIN

PREPARE STMT FROM 
"DELETE FROM `tbl` WHERE tbl_ref_product = ? AND id_table = @id_post";

SET @id_post = p_id_post; 
SET @Tbl_ref = p_Tbl_ref;

EXECUTE STMT USING @Tbl_ref;
DEALLOCATE PREPARE STMT;

END

... but same result... any idea? Thanks much in advance!

Upvotes: 0

Views: 2890

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179054

The problem you describe makes absolutely no sense based on the procedure definition, so, if you hear the sound of hoofbeats, and you're sure it isn't horses, you might want to check for zebras:

Try show triggers;.

The next likely cause is that the procedure is actually working fine, but the table tbl has a BEFORE DELETE or AFTER DELETE trigger defined, and that trigger has a design error, referencing a nonexistent column referred to as `pa`.

Upvotes: 1

Indranil.Bharambe
Indranil.Bharambe

Reputation: 1498

you are using column name as table in delete statement. as table is predefined in sql , it is giving you error. try following code 

`CREATE PROCEDURE deletePost(IN p_id_post INT, IN p_Tbl_ref CHAR(2))
BEGIN

SET @id_post = p_id_post; 
SET @Tbl_ref = p_Tbl_ref;

DELETE FROM `tbl` WHERE [table] = @Tbl_ref AND id_table = @id_post;

END`

Upvotes: 0

Related Questions