CODEWITHSUNDEEP
mysqlstored-proceduresprocedure
Mr.P
Mr.P

Reputation: 1257

MySQL stored procedure pass select as parameter

could you please give me an advice how to CALL prcd with SELECT results? Or advice me pls better solution.. I am open minded to all working solution

I have a procedure to control inserting data ...

CREATE PROCEDURE control_insert (
)

And I need to pass data from SELECT results to procedure ...

SELECT t.c1, t.c2
FROM table t1
LEFT JOIN other_table t2
ON t1.id = t2.id
WHERE 1=1

The point is, I need to get some data via SELECT (around 6 tables joined to the base table) and I need to do control for each row before insert.. each row should meet some conditions .. if it doesn't meet them, it should just skip it and process next one ...

The procedure should look like:

CREATE PROCEDURE control_insert (
IN v_c1 INT,
IN v_c2 INT
)

BEGIN 
   IF v_c1 > 1 THEN
    INSERT INTO controlled_table (id, type) VALUES (v_c1, v_c2);
   ELSE
     /* do nothing */
   END IF;
END;


CALL control_insert ( SELECT .... );

Could you help me with that? Is there any possibility to do this via MySQL? I can write a PERL skript, but I want to avoid this type of solution ... I just one to do it only in MySQL way

Thank you

EDIT1: I need to check if ID of the SELECT result and LABEL is already in this table for specific date ... this code above is only an example to demonstrate the situation

SOLUTION

I've found the solution ... so for the other visitors:

calling procedure:

CALL controlInsert();

procedure body:

CREATE PROCEDURE controlInsert()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE v_id_dupl INT;
  DECLARE v_label INT;
  DECLARE v_date DATE;
  DECLARE v_type VARCHAR(100);
  DECLARE v_category VARCHAR(255);
  DECLARE v_user VARCHAR(255);
  DECLARE v_country VARCHAR(255);   

  DECLARE c1 CURSOR FOR SELECT id, label, date, type, category, user, country FROM t1 LEFT JOIN ... /* whole select with 6 joins ended by ; */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;       

    ## open cursor
    OPEN c1;

        ## loop through the cursor
        read_loop: LOOP

          ## fetch cursor into variables
            FETCH c1 INTO v_id , v_label, v_date, v_type, v_category, v_user, v_country;

            ## check if there is any record
            IF done THEN
              LEAVE read_loop;
            END IF;  


            ## get count of existing records 
            SELECT count(*) INTO v_id_dupl 
            FROM  
            WHERE 1=1
            AND id = v_id 
            AND label= v_label
            AND date = v_date;


            ## if v_id_dupl = 0 => no rows found  (ok to load)
            IF (v_id_dupl = 0) THEN 

              INSERT INTO target_table (id, label, date, type, category, user, country) 
                          VALUES (v_id , v_label, v_date, v_type, v_category, v_user, v_country);

          END IF; 

        END LOOP;

    CLOSE c1;       

END

Upvotes: 0

Views: 1694

Answers (1)

Russell Uhl
Russell Uhl

Reputation: 4581

If that is all your stored procedure is doing, then you don't actually need it. You can do the whole thing in a single statement:

INSERT INTO controlled_table (id, type)
SELECT t.c1, t.c2
FROM table t1
LEFT JOIN other_table t2 ON t1.id = t2.id
WHERE something = somethingElse
AND t.c1 > 1

Essentially, I've just combined your original query with the INSERT statement in your procedure.

If your procedure is more complex and needs to do multiple operations on each row, then you should look into using a cursor.

Upvotes: 0

Related Questions