[RESOLVED] Stored Proc with vars

[Grofit]

Hey,

Ive got a stored proc that im writing that is used to create entries with alot of default values.

Now all these default values are great, but certain tables need values that are calculated during the procedure. So i need to select from one table to get some values, add some stuff to it then insert it into another one... hes an example

DECLARE calculatedA INT DEFAULT 0;
DECLARE calculatedB INT DEFAULT 0;
DECLARE baseValue INT DEFAULT 10;

SELECT (table.A + baseValue) INTO calculatedA, (table.B + baseValue) INTO calculatedB FROM table WHERE table.typeid = [Some Arg Input From Stored Proc];

// Insert using the calculated vars into another table

So as you can see ive got one table that i need to get values from (that are unique for each typeid) then add/deduct the base value then assign it to another variable for insertion into a new table...

Hope this makes sense... at the moment im getting an error on the select, but im not that experienced with stored procs so i think my assigning to the calculatedX vars is the problem, as it looks a bit odd having multiple INTO statements in there... but i dont know if thats ok... any help would be great!

[Roger_Ramjet]

You're roght, having 2 INTO statements is 'funny' 😃.

What you need is

SELECT (table.A + baseValue) , (table.B + baseValue) INTO  calculatedA, calculatedB ...

By the way, it is probably not necessary to do it that way as you should be able to so it all in 1 query.

CREATE PROCEDURE `db1`.`sp1` (IN _var INT, IN _c INT, IN _d CHAR(50))
DECLARE baseValue INT DEFAULT 10;

BEGIN

INSERT INTO table2 (A, B, C, D) SELECT (A + baseValue), (B + baseValue), _c, _d
FROM table1 WHERE typeid = _var

END

[Grofit]

Cheers!