0

trying to run a query in workbench that will cycle thru all of the site_ids and test my query against each of them. this should be easy but i'm missing something. 

SET @user_id = 449;
SET @getsites.site_id = 11;
WHILE(@getsites.site_id < 535) DO
     SELECT routine goes here
     SET @getsites.site_id = @getsites.site_id + 1;
END WHILE;
Slopeside Creative
  • 8,901
  • 4
  • 17
  • 18
  • 2
    Why can you not just write a simple SQL statement like `select ... from table where user_id = 449 and site_id between 11 and 534`? – zedfoxus Oct 15 '15 at 15:23
  • will help me pin down the problem site if the query runs until it bombs, vs. just bombing right away on the whole query, which it does now. – Slopeside Creative Oct 15 '15 at 15:27
  • 1
    You can still do that by running `select ... between 11 and 272` and `select ... between 273 and 534`. See which one fails. If first one fails, run `select ... between 11 and 142` and `select ... between 143 and 272` and so on. That's like binary search and you might find the problem in fewer steps than running sequentially looking for failures. – zedfoxus Oct 15 '15 at 15:33
  • If you still decide to do what what you are doing, I'd recommend [stored procedure](http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx) and [select into](http://stackoverflow.com/questions/3075147/select-into-variable-in-mysql-declare-causes-syntax-error) – zedfoxus Oct 15 '15 at 15:35
  • the query only accepts one site_id not a range so just wanted to do a simple loop or while – Slopeside Creative Oct 15 '15 at 15:43
  • these example show it should be possible, however none of them work in Workbench https://www.safaribooksonline.com/library/view/mysql-stored-procedure/0596100892/ch04s03.html – Slopeside Creative Oct 15 '15 at 15:56
  • May we ask what the actual problem is when the query you want to test "bombs"? It might be easier than you think to tell what's going wrong... – Matt Gibson Oct 15 '15 at 16:17
  • it is a correlated query that just stopped working after 6 months of heavy use. I'm trying to figure out what set of data is the issue and it'll be tied to a specific site or sites. – Slopeside Creative Oct 15 '15 at 18:23

1 Answers1

0

I am sharing a proc block, you can convert this with your requirements.

DELIMITER $$
USE test$$ -- database name
DROP PROCEDURE IF EXISTS proc_name$$
CREATE PROCEDURE proc_name()
BEGIN
DECLARE user_id INT(3) DEFAULT 0;
DECLARE site_id INT(4) DEFAULT 0;
SET site_id = 11;
REPEAT
    SELECT 1; -- routine goes here [execute your logic here]
    SET site_id = site_id + 1;
UNTIL site_id < 535 END REPEAT;
SELECT "Proc completed" AS Result;
END$$

DELIMITER ;

First you try with SQL, if not possible then try procedure.

Adarsh Gangadharan
  • 653
  • 4
  • 10