In out parameter not working properly in Mysql

I have make one procedure which has in and out parameter:

delimiter //
DROP PROCEDURE IF EXISTS empInfo ; 
CREATE PROCEDURE empInfo (tblname VARCHAR(50),
                          clName VARCHAR(50),
                          out total INT)
BEGIN

 SET @s = CONCAT("SELECT count(", clName ,") into total FROM ", tbl开发者_JAVA百科name );
 PREPARE stmt FROM @s;
 EXECUTE stmt;

END//

Here I'm going to call above procedure in mysql.

mysql> CALL empInfo('emp','empid', @total);

It creates successfully, but when I use it - it doesn't work properly.

---

seems alright to me but just dun work somehow
here is the workaround :

delimiter //
DROP PROCEDURE IF EXISTS empInfo ;
CREATE PROCEDURE empInfo (in tblname VARCHAR(255), in clName VARCHAR(255))
BEGIN
  SET @s = CONCAT("SELECT count(", clName ,") into @total FROM ", tblname);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
END//
delimiter ;
CALL empInfo('emo','empid');
select @total;

---

UPDATE : THE STORED PROCEDURE LIKE :

DELIMITER //
DROP PROCEDURE IF EXISTS empInfo //
CREATE PROCEDURE empInfo ( IN tblname VARCHAR(50),
                          IN clName VARCHAR(50)
                         )
READS SQL DATA
BEGIN


 SET @s = CONCAT("SELECT count(", clName ,") as counter  FROM ", tblname);
 PREPARE stmt FROM @s;
 EXECUTE stmt;

END//

THE CALL IS LIKE

CALL empInfo('emp','empid');

---

I've been running into the same situation. I wanted to generate a prepared statement and return a value (my natural inclination was to use a function, however apparently you cannot use PREPARE and EXECUTE from within functions)

SQL syntax for prepared statements cannot be used within stored routines (procedures or functions), or triggers. This restriction is lifted as of MySQL 5.0.13 for stored procedures, but not for stored functions or triggers. http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

A slight variation to the solution posted by @ajreal, after you execute the statement you can set the value of your OUT param to the value of session variable used in the query.

DELIMITER //
DROP PROCEDURE IF EXISTS empInfo;
CREATE PROCEDURE empInfo (in tblname VARCHAR(255), in clName VARCHAR(255), out total INT)
BEGIN
  SET @s = CONCAT("SELECT count(", clName ,") into @temp_Total FROM ", tblname);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  SET total = @temp_Total; 
END//
DELIMITER ;
CALL empInfo('emo','empid', @total);
select @total;