passing a table as a INPUT parameter to a stored procedure
Posted by:
B Aneja
Date: June 17, 2016 02:18AM
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.