Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

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
0
SAbboushi
Asked:
SAbboushi
  • 3
  • 2
  • 2
1 Solution
 
Randy DownsOWNERCommented:
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".
0
 
SAbboushiAuthor Commented:
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...
0
 
nemws1Commented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Randy DownsOWNERCommented:
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

0
 
SAbboushiAuthor Commented:
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

0
 
nemws1Commented:
I think you can get around this issue by declaring a new CONTINUE HANDLER within your while block.  I'm not sure if you need to define a new block or not (I haven't tested, yet), but I would try this:

1) Line after "DECLARE Done..." add:
DECLARE Done_Inner BOOLEAN DEFAULT FALSE;

2) On the line after "WHILE...DO" add:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done_Inner := TRUE;

And then never check/ignore it.  I'm fairly certain this will work, as the handlers work only within their block and are temporarily overwritten in sub-blocks.


If this does *not* work, remove the new DECLARE .. HANDLER we jut added and do this (create a new sub-block that just contains the WHILE LOOP block):

1) *Before* the "WHILE...DO" line add:
WHILEBLOCK: BEGIN
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done_Inner := TRUE;

2) On the line after "END WHILE;" add:
END WHILEBLOCK;
0
 
SAbboushiAuthor Commented:
Good stuff - thanks!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now