Unknown system variable when creating procedure in MySQL

Question

I don't know how to create a stored procedure and using select in while statement. This query will come to this error "unknown system variable 'tableview'". Anyone help me please to repair it. Thanks for your helping.

CREATE PROCEDURE tablebts()
BEGIN
    DECLARE x INT;
    SET x=0;
    SET tableview;

    WHILE x < 10 DO
        SET x=x + 1;
        SET tableview = SELECT idbts 
                        FROM (SELECT idbts, namesite, COUNT(idbts) AS jumlah 
                              FROM (SELECT ts.site_id AS idbts, tps.msisdn AS msisdn, tb.site_name AS namesite 
                                    FROM tb_profile_subscribers tps
                                    INNER JOIN tb_sitemap ts 
                                       ON tps.lac=ts.lac2g 
                                       or tps.lac=ts.lac3g 
                                      AND tps.cellid=ts.ci2g 
                                       OR tps.cellid=ts.ci3g_850 
                                       OR tps.cellid=ts.ci3g_2100_1
                                       OR tps.cellid=ts.ci3g_2100_2 
                                       OR tps.cellid=ts.ci3g_2100_3 
                                    INNER JOIN tb_bts tb 
                                       ON ts.site_id=tb.site_id 
                                    GROUP BY msisdn) msisdn 
                              GROUP BY idbts 
                              ORDER BY jumlah DESC 
                              LIMIT 1 OFFSET x) AS tabelbts
    END WHILE;

    SELECT tableview;
END$$

I also put x variable in tableview query (the word "offset x"). Is that true? Help me please and thanks in advance

Like @SalmanA said you forgot to declare the `tableview` property. But whats your goals with this query? Because you override constantly the `tableview` variable and so basically you only return the 10th record? — Yoram de Langen, Nov 22 '18 at 07:24
If you return a value, it would be better to create a `FUNCTION` — gaborsch, Nov 22 '18 at 07:35
You may find this a useful read https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql — P.Salmon, Nov 22 '18 at 07:35
@Salman A I don't wanna make the table view as integer, it looks like a create view of table. There's no problem when I try the select query, the problem when I make it a while for looping the offset in that query. Do you have another suggestion without using procedure? — MeyLin, Nov 22 '18 at 07:55
@Yoram, sorry I was wrong. It's not set query but set tableview and it's still wrong — MeyLin, Nov 22 '18 at 07:56
You appear to be attempting to create a table but MYSQL: does not have table variables , the closest mysql gets is temporary tables. — P.Salmon, Nov 22 '18 at 08:01
@P.Salmon should I declare the database name too although I have use syntax use databasename? — MeyLin, Nov 22 '18 at 08:03
I don't know what you are trying to do so the best I can say is maybe. — P.Salmon, Nov 22 '18 at 08:29
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) — Juan Carlos Oropeza, Nov 22 '18 at 14:09
@JuanCarlosOropeza thanks, I'm so sorry. But at least I have solved this problem. Thanks for all — MeyLin, Nov 23 '18 at 03:34

Juan Carlos Oropeza · Answer 1 · 2018-11-22T14:10:12.200

1

Not related to your problem but there are somethings you need to fix too.

You have to use parenthesis to force correct operator order

Right now you have A OR B AND C OR D Because AND have precedence you really have

 A OR ( B AND C ) OR D

instead you need

(A OR B) AND (C OR D)

Or you can use IN operator to improve reading

INNER JOIN tb_sitemap ts 
   ON tps.lac IN ( ts.lac2g, ts.lac3g )
  AND tps.cellid IN ( ts.ci2g, ts.ci3g_850, ts.ci3g_2100_1, 
                      ts.ci3g_2100_2, ts.ci3g_2100_3 )

edited Nov 22 '18 at 14:10

answered Nov 22 '18 at 07:31

Juan Carlos Oropeza

47,252
12
78
118

Thanks for ur helping. But there's no problem about select query. It's success to view a table. The problem is just about looping the offset value (x). Do u have any suggestion? Thanks before – MeyLin Nov 22 '18 at 08:00
But in the brackets u have explained me, just be used for "AND". I make it as "OR" – MeyLin Nov 22 '18 at 08:45

Unknown system variable when creating procedure in MySQL

1 Answers1