MySQL Forums :: Stored Procedures :: ROW_COUNT() results in NULL from stored procedure


Advanced Search

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
ROW_COUNT() results in NULL from stored procedure 2924 Frank Yingst 05/18/2016 06:32PM
Re: ROW_COUNT() results in NULL from stored procedure 377 Peter Brawley 05/18/2016 07:59PM
Re: ROW_COUNT() results in NULL from stored procedure 417 Frank Yingst 05/18/2016 10:06PM
Re: ROW_COUNT() results in NULL from stored procedure 395 Frank Yingst 05/18/2016 08:21PM
Re: ROW_COUNT() results in NULL from stored procedure 391 Peter Brawley 05/19/2016 10:17AM
Re: ROW_COUNT() results in NULL from stored procedure 342 Rick James 06/01/2016 11:49AM


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.