MySQL Forums
Forum List  »  Stored Procedures

Re: ROW_COUNT() results in NULL from stored procedure
Posted by: Frank Yingst
Date: May 18, 2016 10:06PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: ROW_COUNT() results in NULL from stored procedure
504
May 18, 2016 10:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.