Solved

How to handle 0 rows returned in prepared statement

Posted on 2013-06-14
7
641 Views
Last Modified: 2013-06-14
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
Comment
Question by:SAbboushi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 30

Expert Comment

by:Randy Downs
ID: 39248178
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
 

Author Comment

by:SAbboushi
ID: 39248307
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
 
LVL 23

Expert Comment

by:nemws1
ID: 39248353
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 30

Expert Comment

by:Randy Downs
ID: 39248373
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
 

Author Comment

by:SAbboushi
ID: 39248418
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
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 39248697
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
 

Author Comment

by:SAbboushi
ID: 39248763
Good stuff - thanks!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question