Re: passing a table as a INPUT parameter to a stored procedure

[B Aneja]

delimiter $$

drop procedure if exists dyn_sql$$

CREATE procedure dyn_sql(in_table_name VARCHAR(100)
,out output_table varchar(30)
,out status_code INT
,out status_msg varchar(100))

BEGIN
DECLARE l_sql_stmt varchar (1000);
DECLARE l_student_id INT;


BEGIN

SET @l_student_id=in_student_id;

SET @l_sql_stmt = CONCAT('CREATE TABLE TEMP_TABLE AS '
' SELECT S.STUDENT_ID,SC.SUM(SCORE),SC.AVG(SCORE),SC.MAX(SCORE),SC.MIN(SCORE) '
' FROM STUDENT S,', in_table_name,' AS SCORE SC '
' WHERE S.STUDENT_ID = SC.STUDENT_ID '
' AND S.STUDENT_ID=? '
' GROUP BY SC.EVENT_ID ');

SELECT @l_sql_stmt;

prepare stmt from @l_sql_stmt;
execute stmt using @l_student_id;

SET OUTPUT_TABLE :='TEMP_TABLE';
SET status_code :=0;
SET STATUS_MSG :=' SUCCCESS!';

END;

END$$
DELIMITER ;