Select query output in mysql and differes between SP and query execution

Question

Why is my query output different between SP and query execution. I am executing a simple select query to assign some value..

SP:

CREATE DEFINER=`root`@`localhost` PROCEDURE `INS_EXPENSES`(
IN CATNAME varchar(100),
IN SUBCATNAME varchar(100),
IN AMOUNT INT,
IN USERID INT,
IN DESCRIPTIONS varchar(100)
)
BEGIN

SELECT SUBCATNAME;

SELECT * FROM SUBCAT WHERE SUBCATNAME='MISC';

END

THE ABOVE SELECT QUERY RETURNS ALL DATA FROM THE SUBCAT TABLE. WHERE IN SAME QUERY WORKS FINE IF EXECUTED OTHERWISE.

Answer 1

Your procedure has parameter SUBCATNAME and the WHERE-clause is using that value instead of the column with same name when it compares with the "MISC"-string.

You should use a prefix for the procedure parameters so that they would not get mixed up with column names (use parameter names like in_SUBCATNAME or similar)