MySQL Forums
Forum List  »  Stored Procedures

Stored Procedure fails to populate first column, plus a strange error
Posted by: Raul Espinosa
Date: April 04, 2018 05:33AM

I'm using nested stored procedures to produce a table containing several pieces of information about book publishers.

Code follows:

DELIMITER //

/* THE INNER PROCEDURE */

CREATE PROCEDURE spGetPubStats(IN pubCode CHAR(3), OUT publisherName CHAR(25), OUT num_of_distinct_authors INT,
OUT distinct_books_published INT, OUT book_highest_num_on_hand CHAR(40), OUT num_on_hand INT, OUT total_books_on_hand INT)
BEGIN

/* First, check whether the Publisher code passed in
exists in the Database */
IF EXISTS
(
SELECT publisherCode
FROM Publisher
WHERE publisherCode = pubCode
)

/* If the Publisher Code IS in the database,
calculate the required statistics */
THEN
BEGIN

/* 1.) Set the publisherName */

SET publisherName =
(SELECT publisherName
FROM Publisher
WHERE publisherCode = pubCode);

/* 2.) Set the number of distinct authors who have
written books for the publisher */

SET num_of_distinct_authors =
(SELECT COUNT(Author.authorNum)
FROM ((Author INNER JOIN Wrote ON Author.authorNum = Wrote.authorNum) INNER JOIN Book ON Wrote.bookCode = Book.bookCode)
INNER JOIN Publisher ON Book.publisherCode = Publisher.publisherCode
WHERE Publisher.publisherCode = pubCode);

/* 3.) Set the number of different books published by this publisher */

SET distinct_books_published =
(SELECT COUNT(bookCode)
FROM Book
WHERE publisherCode = pubCode);

/* 4.) AND 5.) Set the title of the book published by this publisher that has
the highest number of onHand units collectively in all branches
of Henry Books AND Set number of OnHand books for the book mentioned*/

SELECT *
FROM
(
SELECT MaxTitle, MAX(OnHandUnits)
FROM
(
SELECT Inventory.BookCode, SUM(OnHand) AS OnHandUnits, Book.title AS MaxTitle
FROM (Inventory INNER JOIN Branch ON Inventory.BranchNum = Branch.branchNum)
INNER JOIN Book ON Inventory.BookCode = Book.bookCode
WHERE Inventory.BookCode IN
(
SELECT DISTINCT Inventory.BookCode
FROM ((Book INNER JOIN Publisher ON Book.publisherCode = Publisher.publisherCode) INNER JOIN Inventory
ON Book.bookCode = Inventory.BookCode) INNER JOIN Branch ON Inventory.BranchNum = Branch.branchNum
WHERE Publisher.publisherCode = pubCode
)
GROUP BY Inventory.BookCode
ORDER BY OnHandUnits DESC
) AS MaxOnHandTitle
) AS SelectorStatement
INTO book_highest_num_on_hand, num_on_hand;

/* 6.) Set the cumulative sum of onHand books from all branches
for all books published by this publisher */

SET total_books_on_hand =
(SELECT SUM(OnHand)
FROM ((Inventory INNER JOIN Branch ON Inventory.BranchNum = Branch.branchNum) INNER JOIN Book
ON Inventory.BookCode = Book.bookCode) INNER JOIN Publisher ON Book.publisherCode = Publisher.publisherCode
WHERE Publisher.publisherCode = pubCode);

END;

/* Otherwise, notify the caller */
ELSE
BEGIN
/* Raise an error signal and provide descriptive error text */
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The specified publisher code was not found in the database.';
END;

END IF;
END //

/* THE OUTER PROCEDURE */

CREATE PROCEDURE spGetAllPubStatsRE()
BEGIN

/* Variable that will store the
publisher codes that will be passed to
the inner function */
DECLARE pubCode CHAR(3);

/* Cursor that will iterate through all the
publisher codes in the Publisher table */
DECLARE pub_cursor CURSOR FOR
SELECT publisherCode
FROM Publisher;

/* First, drop permanent database table
containing publisher stats if it exists */
DROP TABLE IF EXISTS Publisher_Stats;

/* Create the permanent database table
(or recreate it if it was deleted above */
CREATE TABLE Publisher_Stats
(
publisherName CHAR(25),
num_of_distinct_authors INT,
distinct_books_published INT,
book_highest_num_on_hand CHAR(40),
num_on_hand INT,
total_books_on_hand INT
);

/* Open the cursor so we can use it */
OPEN pub_cursor;

/* Loop for iterating through the publisher codes */
LOOP

/* Fetch each Publisher code into pubCode */
FETCH pub_cursor INTO pubCode;

/* Call the inner procedure, passing
it the current publisher code as input */

CALL spGetPubStats(pubCode, @publisherName, @num_of_distinct_authors, @distinct_books_published,
@book_highest_num_on_hand, @num_on_hand, @total_books_on_hand);

/* Store the results of spGetPubStats
in the Publisher_Stats table */
INSERT INTO Publisher_Stats(publisherName, num_of_distinct_authors, distinct_books_published,
book_highest_num_on_hand, num_on_hand, total_books_on_hand)
SELECT @publisherName, @num_of_distinct_authors, @distinct_books_published, @book_highest_num_on_hand,
@num_on_hand, @total_books_on_hand;

END LOOP;

/* At this point, we're done with
the cursor, so close it */
CLOSE pub_cursor;
END //

/* CHANGE DELIMITER BACK */
DELIMITER ;

I'll preface my explanation by saying that the queries numbered (in the comments) 1.) through 6.) do exactly what they're supposed to do.

I wrote individual test queries for each one and I've spent the last hour and a half manually checking that they all return the proper values for every publisherCode.

The problem is that when I call the outer procedure (which creates the table Publisher_Stats that stores the data) and then attempt to view it using a SELECT statement like so:

CALL spGetAllPubStatsRE;

SELECT *
FROM Publisher_Stats;

I get the following error:

Error Code: 1329. No data - zero rows fetched, selected, or processed

And the table isn't displayed.

HOWEVER, there's two interesting caveats here: first, the statement doesn't appear to actually have failed to execute, despite what the Error Code seems to be saying (looks like it's saying "nothing was done", but I'm not sure). The Publisher_Stats table DOES get created, and after I CALL spGetAllPubStatsRE, if I comment out the call and just leave the

SELECT *
FROM Publisher_Stats;

and execute that, the table displays properly.

The second problem is with the values in the table. Every column is populated with the correct values EXCEPT for the first one, publisherName, which has nothing but NULLs all the way down.

I use user-defined variables to store the values calculated by the stored procedure, and there doesn't seem to be anything wrong with the way I set the publisherName variable up:

SET publisherName =
(SELECT publisherName
FROM Publisher
WHERE publisherCode = pubCode);

This is the exact same way I set all my other user-defined variables (as you can see), and they all populate properly. At first I thought it might have something to do with publisherName having the same name as the column in the Publisher table, so I tried changing the name of the user-defined variable to pubName, but that didn't fix the problem.

Why is this happening?

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored Procedure fails to populate first column, plus a strange error
431
April 04, 2018 05:33AM


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.