passing a table as a INPUT parameter to a stored procedure

[B Aneja]

Below is an assignment, below that is my solution to the assignment. I only cannot get table score to be passed through as an input parameter as asked in part a. of the problem. Could someone please tell me what changes I can make to my code to make it work like asked in the assignment.


******************************************************************


Write a procedure using dynamic SQL as follows.

a. The procedure accepts a table name as input parameter. (Table SCORE is passed as input parameter).
b. The procedure should have three OUT parameters which will print the status_message as “Success”, status_code as “0” and “output_table”.
c. Write a dynamic sql by constructing the passed in table name and compute the SUM, AVG, MAX, MIN of scores by using group by on event_id.
d. After executing the procedure, store the results into a temporary table and return the table name in the output parameter.
e. When you print the output_table parameter you should see the table name with the calculated scores.



***************************************************************



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, 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 ;



**************************************************************

Thank you so much in advance. I am very grateful either ways.