MySQL Create view with stored procedure

Monday, June 30, 2014

Evening kind sirs/madames;

I'm attempting to create a stored procedure that updates a view in MySQL. In a nutshell, when the trainingType database gets updated, the view trsReq (which is essentially a pivot table of employees by trainingType. Training records are stored in a view, trs) needs to get updated.

SET @sql = NULL;
SET @@group_concat_max_len = 5000;
SELECT GROUP_CONCAT(DISTINCT CONCAT('max(IF(type =',id,', data, "")) AS "',shortDesc,'",max(IF(type =',id,', trs.status, "")) AS "',shortDesc,'_status"')) into @sql FROM trainingType where required=1;
SET @sql = CONCAT('create view trsReq as SELECT fName, lName, empId, ', @sql, ' FROM trs left join employee on where employee.inactive=0 GROUP BY empId order by lName, fName limit 100');
PREPARE stmt FROM @sql;

Executing that set of commands results in this: ERROR 1142 (42000): ANY command denied to user 'user'@'localhost' for table '/var/mysqltmp/#sql_285f_0'