asked on

How to handle 0 rows returned in prepared statement

I am using a prepared statement:

SET @MyQuery := CONCAT("SELECT 1 INTO @SVWexists FROM Quran.rootwordattributesrange WHERE SVW = ", MySVW);

Open in new window

If 0 rows is returned, then the value of @SVWexists seems to remain the same value it was prior to executing the statement, maybe because the result returned is/has a warning (I suspect it is returning "0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed")

1) How do I elegantly code for this in a stored procedure?
2) How can I see the response when the statement is executed in a stored procedure?

Thanks--
Sam

Randy Downs

You could just set @SVWexists to 0 before executing the SP. Then check if it's still 0 at the end of the procedure. If it is then exit gracefully with a message like "No rows found".

SAbboushi

ASKER

Thanks -- I appreciate you responding.

Great idea! It seems though that the select statement returning 0 rows messes up my error handling...

I am using a cursor with:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done := TRUE;

Open in new window

and a LOOP that is exited (when cursor reaches the end) by:

		IF Done THEN 
			LEAVE Cursor_Loop; 
		END IF;

Open in new window

It seems that when the select statement returns 0 rows, this also sets Done to True resulting in my LOOP being exited.

I am looking for some error handling for the select statement that doesn't interfere with the existing CONTINUE HANDLER...

Nem Schlecht

Can you post more of your code? At first I was going to suggest setting @SVWexists to *NULL* initially and not zero, but I don't think that's your problem.

Randy Downs

You want some logic like an IF else statement to execute the meat of your script if rows returned are not 0.

IF rows not = 0 
 execute code
Else 
 Message "No rows to work with"
ENDIF

Open in new window

SAbboushi

ASKER

Thanks-

Number-1: I get the logic, it's the code I need ; )

nemwsS1: code below:

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_rootwordattributesrangerows`(IN ClearFlag CHAR(1))
BEGIN 

	DECLARE MyID INTEGER;
	DECLARE MySV VARCHAR(7);
	DECLARE MySVW VARCHAR(11);
	DECLARE MysWID INTEGER;
	DECLARE MyeWID INTEGER;
	DECLARE MySubString VARCHAR(1000);
	DECLARE Myrangemarker CHAR(1);
	DECLARE i INTEGER;
	DECLARE Done BOOLEAN DEFAULT FALSE; -- For CONTINUE statement

	DECLARE MyCursorC CURSOR FOR
		SELECT ID, SV, sWID, eWID FROM Quran.rootwordattributes;

	DECLARE MyCursor CURSOR FOR
		SELECT ID, SV, sWID, eWID FROM Quran.rootwordattributes WHERE rangecreated IS NULL;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done := TRUE; 


	IF ClearFlag = "C" THEN

		TRUNCATE Quran.rootwordattributesrange;

		OPEN MyCursorC;

	ELSE

		OPEN MyCursor;
		
	END IF;
	
	Cursor_Loop:
	LOOP

		IF ClearFlag = "C" THEN

			FETCH MyCursorC INTO MyID, MySV, MysWID, MyeWID;
		
		ELSE

			FETCH MyCursor INTO MyID, MySV, MysWID, MyeWID;

		END IF;

		IF Done THEN 
			LEAVE Cursor_Loop; 
		END IF; 

		SET i := MysWID;

		WHILE i <= MyeWID DO
		
			SET MySVW := concat(MySV, ':', i);

			IF i = MysWID THEN 

				SET Myrangemarker := "S";

			ELSEIF i = MyeWID THEN

				SET Myrangemarker := "E";

			ELSE

				SET Myrangemarker := "M";

			END IF;

			SET @SVWexists := NULL;
			SET @MySVW = MySVW;

			SET @MyQuery := CONCAT("SELECT 1 INTO @SVWexists FROM Quran.rootwordattributesrange WHERE SVW = '", MySVW, "'");
			PREPARE MyQuery from @MyQuery; 
			EXECUTE MyQuery;
			DEALLOCATE PREPARE MyQuery;

			IF @SVWexists IS NULL THEN
			
				SET @MyQuery := CONCAT("INSERT INTO Quran.rootwordattributesrange(SVW, rangemarker) VALUES('", MySVW, "','", Myrangemarker, "')");
				PREPARE MyQuery from @MyQuery; 
				EXECUTE MyQuery;
				DEALLOCATE PREPARE MyQuery;

			END IF;

			SET @MyQuery := CONCAT("UPDATE Quran.rootwordattributes AS RWA SET RWA.rangecreated = ", '"', "Y", '"', " WHERE ID = ", MyID);
			PREPARE MyQuery from @MyQuery; 
			EXECUTE MyQuery;
			DEALLOCATE PREPARE MyQuery;
			
			SET i := i + 1;
			
		END WHILE;

	END Loop 
	Cursor_Loop;

END

Open in new window

ASKER CERTIFIED SOLUTION

Nem Schlecht

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SAbboushi

ASKER

Good stuff - thanks!