Re: ROW_COUNT() results in NULL from stored procedure
DROP PROCEDURE IF EXISTS cronInsert;
delimiter //
create procedure cronInsert(IN p_database varchar(100), IN p_eventsTable char(14), IN p_evDate date, IN p_evTime time, IN p_evDesc blob,
IN p_evType varchar(40), IN p_defname varchar(100), IN p_evCity char(4), IN p_evFrom char(2), IN p_casenum char(9),
OUT p_rowsAffected INT, OUT p_sql text)
BEGIN
SET p_rowsAffected = -101;
SET @count1 = (SELECT count(*) FROM court_links WHERE cronjobTable = p_eventsTable);
SET @sql2 = CONCAT("SET @nextID = (SELECT MAX(evID)+1 FROM ",p_database,".",p_eventsTable,")");
PREPARE stmt FROM @sql2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET p_rowsAffected = @nextID;
SET @count1 = 1; -- test code only, take out later
IF @count1 > 0 THEN
SET @sql = "INSERT INTO ";
SET p_sql = @sql;
SET @sql = CONCAT(@sql,p_database,".",p_eventsTable," (evDate, evTime, evID, evDesc, evCity, evFrom, evType, defname, casenum) ");
SET @sql = CONCAT(@sql, " VALUES (");
SET @sql = CONCAT(@sql, "'",p_evDate,"',");
SET @sql = CONCAT(@sql, "'",p_evTime,"',");
SET @sql = CONCAT(@sql, "'",@nextID,"',");
SET @sql = CONCAT(@sql, "'",p_evDesc,"',");
SET @sql = CONCAT(@sql, "'",p_evCity,"',");
SET @sql = CONCAT(@sql, "'",p_evFrom,"',");
SET @sql = CONCAT(@sql, "'",p_evType,"',");
SET @sql = CONCAT(@sql, "'",p_defname,"',");
SET @sql = CONCAT(@sql, "'",p_casenum,"',");
SET @sql = SUBSTRING(@sql,1,LENGTH(@sql)-1);
SET @sql = CONCAT(@sql,')');
SET p_sql = @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET p_rowsAffected = ROW_COUNT();
END IF;
END
//
delimiter ;
calling statement:
set @x = 1;
set @xx = 'whatever';
call cronInsert('tv_events','bf_events','2016-05-18','08:59:00',' \n \n 08:59 AM INITIAL APPEARANCE','INITIAL APPEARANCE','XXXXX, BBBBB','bf','t','161800173',@x,@xx);
select @x;
select @xx;
0 is always returned for the rows affected