Varies result for SQL function and Query

Question

I have a SQL function that contains a simple Select Count query, and it gives the correct result when running the query in SQL mode whereas the result is different when the same query is used in a function. Both scenarios are as here;

1. SQL Query

SELECT COUNT(*) FROM responses WHERE batch='2K18' AND degree_program='BSC' ;

2. SQL Function

BEGIN
       DECLARE ResponseCount INTEGER;
       SET ResponseCount = (SELECT COUNT(*) FROM responses WHERE batch=BATCH AND degree_program=DEGREEPROGRAM);
    RETURN (ResponseCount);
END

The result of SQL Query is correct whereas when used in the SQL Function it only checks the second parameter of where clause. what could be possible solution.

Answer 1

To me, this is suspicious:

WHERE batch=BATCH AND degree_program=DEGREEPROGRAM); 
      -----------
      this

How come? Because you named column and parameter the same, so it evaluates to something similar to where 1 = 1 which is always true, i.e. you don't filter data on the 1st parameter at all.

On the other hand, the 2nd parameter's name differs from column name (which has underline in its name).

Generally speaking, it is good if you accept naming conventions according to best practices and name parameters using prefix as par_ or i_ (as "in" parameter) or anything else you find appropriate.

Therefore, if your query looked like

... SET ResponseCount = (SELECT COUNT(*) 
                         FROM responses r 
                         WHERE r.batch          = PAR_BATCH 
                           AND r.degree_program = PAR_DEGREE_PROGRAM);

it might work as you expected.