Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

The situation is that I have a mysql stored procedure that has some variables and I want to use their value based on a SELECT that I do to get the list of variable I really need in a loop:

MyTable(varId, varName, typeId)  
(0,var1,1)  
(1,var2,2)

MyValues(MyVar1,MyVar2)
(1,2)

Here is what I'd like to do :

DECLARE var1 ;  
DECLARE var2 ;  

SELECT MyVar1,MyVar2 INTO var1,var2 FROM MyValues; 
CURSOR curs FOR  
SELECT varName FROM Mytable WHERE typeId = 1;

LOOP  
FETCH curs INTO varName;  
  SELECT sqrt(varName.value);  //Should be 1 because var1 =1;
END LOOP;

It seems to be impossible to do it with prepared functions unless I use session variables but actually I receive the var1 and var2 dynamically (in a curs) and I cannot fetch it into session variables.

Can anybody give me some ideas to solve this issue? Thanks a lot!

share|improve this question

1 Answer

up vote 0 down vote accepted

First, why do you want to store the variables in SESSION? If you can get them inside the procedure, you can also use them inside the procedure.

Again, if you need to return them, you can return a result-set inside your procedure, and catch them in your web-programming or application. Like:

SELECT @var1, @var2

One more thing, I am not really sure I got you right, but if you want the variables from outside(your web-application), you can just pass them as parameters as well. like:

$mysqli->query("CALL procedure_name($parameter1, $parameter2)");

After Question Edit: I do not understand this..

CURSOR curs FOR  
SELECT varName FROM Mytable WHERE typeId = 1;

LOOP

Why use cursor here just to select? Anyway, I think, you want to use 'varName' next line. You can store it in another variable like:

DECLARE a_variable VARCHAR(50);
SELECT varName INTO a_variable FROM Mytable WHERE typeId = 1;

Here you get the value inside *a_variable* Use it:

SELECT sqrt(a_variable);//NOT like varName.value

Does this answer your question? Please let me know.

EDIT #3: I got your question wrong here:

//Should be 1 because var1 =1;

Here is the code you need to use:

SET @query = CONCAT('select SQRT(@', var_name, ') as square_root');
     -- SELECT @query;
    PREPARE stmt FROM @query;
    EXECUTE stmt;

Here is the full procedure I tried this with:

DELIMITER $$

CREATE PROCEDURE `test_var`(
)
BEGIN
    DECLARE var1 int;  
    DECLARE var2 int; 
    DECLARE var_name varchar(50);

    SELECT MyVar1,MyVar2 INTO @var1,@var2 FROM MyValues;
    SELECT varName INTO var_name FROM Mytable WHERE typeId = 1;

    SET @query = CONCAT('select SQRT(@', var_name, ') as square_root');
     -- SELECT @query;
    PREPARE stmt FROM @query;
    EXECUTE stmt;

END

Hope, this helps. Please let me know.

Regards Mayukh

share|improve this answer
Thank you for your answer, I think I wasn't really clear.. I get nothing from an application, I get the variable names from a table inside the DB(selecting them in the cursor). The way I wrote "varName.Value" was only to express the fact that I wanted to force mysql to consider the value of varName as a variable name. In this case, we have: varName = 'var1'; var1 = 1; And I want to process: SELECT sqrt(1); equivalent to SELECT sqrt(var1); But mysql will process : SELECT sqrt(varName); Equivalent to SELECT sqrt('var1'); (that has no sense..). – Jean Mar 6 at 9:17
And for the session variable, I don't want to use it, it is only because the only begining of solution I figured out was to execute a prepared statement that cannot access to SP variables so I needed to set the varibles in the session instead, but it seems to be not very nice habit and finally cannot use it in a cursor anyway.. – Jean Mar 6 at 9:26
Hi @Jean. Edited my answer. – Mayukh Roy Mar 6 at 10:19
Hi @Mayukh Roy , I am using a cursor because in the real case, there will be several lines out of the select and I want to execute the function on each line one by one. What you wrote will execute: SELECT sqrt('var1'); So a square root of a string instead of square root of the value pointed by the string I think. – Jean Mar 6 at 11:52
1  
The problem of session variables is that I am getting a lot of var from a cursor and mysql forbids to put them directly into session variables because they claim it is a bad habit..(there are some tricks to do it, but if is not clean, I prefer avoid.) – Jean Mar 7 at 9:26
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.