Looping Over Result Sets in MySQL

by

Something like this should do the trick (However, read after the snippet for more info)

CREATE PROCEDURE GetFilteredData()
BEGIN
  DECLARE bDone INT;

  DECLARE var1 CHAR(16);    -- or approriate type
  DECLARE var2 INT;
  DECLARE var3 VARCHAR(50);
  
  DECLARE curs CURSOR FOR  SELECT something FROM somewhere WHERE some stuff;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  DROP TEMPORARY TABLE IF EXISTS tblResults;
  CREATE TEMPORARY TABLE IF NOT EXISTS tblResults  (
    --Fld1 type,
    --Fld2 type,
    --...
  );

  OPEN curs;

  SET bDone = 0;
  REPEAT
    FETCH curs INTO var1, var2, var3;

    IF whatever_filtering_desired
       -- here for whatever_transformation_may_be_desired
       INSERT INTO tblResults VALUES (var1, var2, var3);
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;
  SELECT * FROM tblResults;
END

A few things to consider…

Concerning the snippet above:

  • may want to pass part of the query to the Stored Procedure, maybe particularly the search criteria, to make it more generic.
  • If this method is to be called by multiple sessions etc. may want to pass a Session ID of sort to create a unique temporary table name (actually unnecessary concern since different sessions do not share the same temporary file namespace; see comment by Gruber, below)
  • A few parts such as the variable declarations, the SELECT query etc. need to be properly specified

More generally: trying to avoid needing a cursor.

I purposely named the cursor variable curs[e], because cursors are a mixed blessing. They can help us implement complicated business rules that may be difficult to express in the declarative form of SQL, but it then brings us to use the procedural (imperative) form of SQL, which is a general feature of SQL which is neither very friendly/expressive, programming-wise, and often less efficient performance-wise.

Maybe you can look into expressing the transformation and filtering desired in the context of a “plain” (declarative) SQL query.

More Related Contents:

  1. Can I create view with parameter in MySQL?
  2. Connect By Prior Equivalent for MySQL
  3. How to raise an error within a MySQL function
  4. MySQL stored procedure vs function, which would I use when?
  5. MySQL stored procedures use them or not to use them
  6. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
  7. MySQL create stored procedure syntax with delimiter
  8. MySQL : transaction within a stored procedure
  9. Writing optional parameters within stored procedures in MySQL? [duplicate]
  10. List of Stored Procedures/Functions Mysql Command Line
  11. passing LIMIT as parameters to MySQL sproc
  12. mysql stored procedure that calls itself recursively
  13. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  14. How do you debug MySQL stored procedures?
  15. How can I use transactions in my MySQL stored procedure?
  16. MySQL error #2014 – Commands out of sync; you can’t run this command now
  17. MySQL Stored Procedures not working with SELECT (basic question)
  18. use a variable for table name in mysql sproc
  19. Split a string and loop through values in MySQL stored procedure
  20. How to schedule a stored procedure in MySQL
  21. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  22. MySQL : When stored procedure parameter name is the same as table column name
  23. What is dynamic SQL?
  24. How to use Table output from stored MYSQL Procedure
  25. How do I write an SP in phpMyAdmin (MySQL)?
  26. Call a stored procedure for each row returned by a query in MySQL
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. is it possible to call a sql script from a stored procedure in another sql script?
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment