Need some Stored Procedure / Cursor Help

I've done a lot with stored procedures in the past, but it was primarily with Informix and Oracle - new to MySQL stored procedures.

I have a bunch of data that I batch load into a flat table and then want a stored procedure to loop through it and move it into the "normalized" data structures.

But I'm having all kinds of troubles getting a simple cursor to work inside a stored procedure. Here is the code I have currently (it is far from complete, but can't even get the "basic" stuff to work yet):

DELIMITER ##
CREATE FUNCTION load_domains(clientID INT, sBatchName VARCHAR(20)) RETURNS INT
  
BEGIN
  
  DECLARE recCtr INT DEFAULT 0;
  DECLARE iBatch INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE done INT DEFAULT 0;
  DECLARE impDt DATE;
  DECLARE impDomain VARCHAR(80);
  DECLARE impGIdx INT;
  DECLARE impLinks INT;
  DECLARE imp_pr INT;
  DECLARE cur1 CURSOR FOR 
    SELECT imp_date, imp_domain, imp_g_idx, imp_links, imp_pr 
    FROM import_domain_data WHERE (imp_pr > 0);
    
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
    
  -- Verify valid client ID was passed in
  SELECT COUNT(*) INTO recCtr FROM user WHERE uid=clientID;
  IF (recCtr = 0) THEN
     RETURN 0;
  END IF;
  
  -- Create Batch Record
  INSERT INTO domain_batches (dbid, dbname, dbdt, dbrec_ctr)
  VALUES (0,sBatchName,NOW(),0);
  SET iBatch = LAST_INSERT_ID();

  
  OPEN cur1;
  
  SELECT FOUND_ROWS() INTO num_rows;
  
  
  REPEAT
    FETCH cur1 INTO impDt, impDomain, impGIdx, impLinks, imp_pr;
  
    IF NOT done THEN
       SET recCtr = recCtr + 1;       
       INSERT INTO domains (did, cid, dname, dcur_dt, dcur_g_pgs, dcur_links, dcur_pr, tmstmp, uid)
       VALUES (0, clientID, impDomain, impDT, impGIdx, impLinks, dcur_pr, NOW(), 1);
    END IF;
  UNTIL done END REPEAT;
  
  CLOSE cur1;
  
  RETURN (recCtr-1);
  
           
END ##

Code (markup):

Now, here's the weird thing. If I simply run this, I get a return code of '0' and it saves the one record into the domain_batches table but never executes the cursor. However, if in the definition of the cursor I simply remove the "WHERE" clause, then it does execute the cursor.

Why would the "WHERE clause" prevent this from working? I have pasted in the exact SQL from the WHERE clause and tested it - their are 13 rows which meet the WHERE condition - there is data there.

The table that I am doing the cursor on does not have an index on it - is this a problem?

Oh, one other oddity... As the code is above, even removing the WHERE clause it gave another error that the 'dcur_pr' could not be NULL. Again, very odd. There is a NOT NULL condition on that field, however, there are NO RECORDS in the import table with a NULL value in that field. If I just "hard code" it to 0 (zero), and remove the WHERE clause on the cursor, this executes and inserts the 200+ records. However, that is obviously *not* the desire and I really don't understand either error...

Very odd...

I would be much appreciative of any help...

 

Consider bold and youll see that the 'oddity' is rather obvious. Try not intermix variable names and field names or let the server know what exactly you are talking about

This is the most obvious oddity. You are trying to assign value of undefied variable dcur_pr to domains.dcur_pr (NOT NULL restricted field with the same name). Note you are using insert not update.
That's why you get error reported.

 

Yep...

Funny how we can't see our own errors sometimes...

Both "simple" ones...

Thanks for the help!