Unanswered: Store Procedure for updating an percentage field
suppose i have two tables, tbPollOptions & tbPollVotes10 (10 is tbl_id, votes store in different tables, tbPollVotes10, tbPollVotes11, tbPollVotes12, ...)
As the name of tables shoes, first one is poll options and second one is submitted user votes.
for keeping summary i want to update a percentage of result at a field in tbPollOptions with a sp.
The sp should first do a COUNT on tbPollVotes and get number of all votes, then update percentage field base on this COUNT & return affected rows.
i wrote the sp till here, can someone complete the sp?
CREATE PROCEDURE `spUpdatePercentage`(IN tbl_id INT)
DECLARE @num_records INTEGER
SET @table_name = CONCAT('tbPollVotes', tbl_id);
SET @sql_text = CONCAT('
SELECT COUNT( * ) AS num
GROUP BY options_id;
PREPARE stmt FROM @sql_text;
@num_records = EXECUTE stmt;
DEALLOCATE PREPARE stmt;
#sp should make query like this:
#SELECT options_id, COUNT(*) FROM tbPollVotes10 GROUP BY options_id;
#C:then it should UPDATE tbPollOptions base on above query result:
normally for doing something like this i make two VIEW for part A & B , and then i use them for update in SP, but i'm in situation that table name are varries, base on different seasons, so i should pass tbl_id, & make all procedure kind of dynamic.