How to get a loop value in MySQL stored procedure?


Problem :

FOR I = 1 TO 10
    tRESNUM = ALLTRIM(STR(I))
    tRESCAS = 'tRESCAS'+tRESNUM         

    &tRESCAS = 0.00

ENDFOR    

I am converting this fox-pro code to MySQL Stored Procedure.

In Foxpro tRESCAS = 'tRESCAS'+tRESNUM gives tRESCAS1, tRESCAS2,.. and &tRESCAS gives the value which is tRESCAS1 or tRESCAS1 or ... something else.

For example

if I =1 =>

FOR I = 1 TO 10
        tRESNUM = ALLTRIM(STR(1))
        tRESCAS = 'tRESCAS'+1       

        tRESCAS1 = 0.00         
ENDFOR

If I =2 =>

FOR I = 1 TO 10
        tRESNUM = ALLTRIM(STR(2))
        tRESCAS = 'tRESCAS'+2       

        tRESCAS2 = 0.00         
ENDFOR

&tRESCAS Automatically take the tRESCAS value tRESCAS1 or tRESCAS2 like this.

In Stored Procedure How Can i get this type of Value?. (is any Special variable in stored procedure like & this?)

Is this details enough?.



Solution :

Have a look at this procedure -

PROCEDURE procedure1()
BEGIN

  DECLARE tRESCAS VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;

  DROP TEMPORARY TABLE IF EXISTS t;
  CREATE TEMPORARY TABLE t(num INT, column1 VARCHAR(255));

  WHILE i < 10 DO
    INSERT INTO t VALUES(i, CONCAT('tRESCAS', i));
    SET i = i + 1;
  END WHILE;

  -- Try to get fifth value
  SELECT column1 FROM t WHERE num = 5;

END

All values are stored into temporary table.

Execute the procedure:

CALL procedure1;
+----------+
| column1  |
+----------+
| tRESCAS5 |
+----------+

Mysql Tutorials

Mysql Howto..

How to save a Chinese charecter 𥚃 in MySQL

How to access values displayed in a php dropdownlist populated from a mysql database

how to get more than one max records in MySQL

How to update MySQL two column lookup table where each field is Primary Key

How to return a column value only once in MySQL

How to use Triggers for Logging History of database changes in MySQL?

How to deploy war file on JETTY server

How to connect to a database (unknown host, no ip)

Error shown while extracting og:image from database on homepage in php?

mysql result show first some products, then the rest

How to do MySQL update query in r

How do I add more members to my ENUM-type column in MySQL?

How to improve speed of this mysql query processing

How to create MySQL Trigger?

MySQL - How is this query performing relational division?

How to log query response time in MySQL?

How to handle millions of records daily with mysql

MySQL Java JDBC: How to get the name of an auto-incremented column?

how to migrate DB from MySql to MS-SQL?

how does the MySQL get space for database?

How to substring WHERE in SELECT php mysql

how to count no of specific symbol in a row in mysql

How to sort data in mysql UNION subquery?

How can I connect to MySQL from windows forms?

How to excute something what is inside a variable in MySQL?

How do delete related data in MySQL tables in a database?

How to organise the MYSQL database?

How to find slower MySQL queries from many small queries

How to deal with Sedna via PHP

How to order by updated date and time varchar Desc