Hello everybody,
Rhino answer to "Evaluating text as an expression" and he solves my
problem too. I need a prepared statement to execute dynamic sql.
I understand that I cannot pass a tablename as a parameter value, since
it's not a value, so I have to prepare my sql separately.
I tried this simple test and it worked,
CREATE PROCEDURE p_select (IN tablen VARCHAR(128))
BEGIN
SELECT CONCAT("SELECT * FROM ", tablen) INTO @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
I don't understand why using a regular procedure variable instead of a
session variable doesn't work:
CREATE PROCEDURE p_select2 (IN tablen VARCHAR(128))
BEGIN
DECLARE m_sql VARCHAR(128);
SELECT CONCAT("SELECT * FROM ", tablen) INTO m_sql;
PREPARE stmt1 FROM m_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'm_sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END' at line 5
Is this a youth problem of stored procedures or an intented behaviour?
anyone has some explanation or a better way to do this?
Thankyou
Stefano Locati - Obliquid
http://software.obliquid.com
Stefano Obliquid wrote:
Hello,
I am moving my first steps with stored procedures in Mysql and I was
trying to write a stored procedure
using a tablename given as a variable. In this example the local
variable m_sequence has the value of the
table I want to use.
CREATE PROCEDURE p_new_id (IN tablename VARCHAR(128), OUT new_id INT)
BEGIN
DECLARE m_new_id INT DEFAULT 0;
DECLARE m_sequence VARCHAR(128);
SELECT CONCAT("_sequence_", tablename) INTO m_sequence;
INSERT INTO m_sequence VALUES (NULL);
SELECT last_insert_id() INTO m_new_id;
DELETE FROM m_sequence WHERE sequence < m_new_id;
SET new_id = m_new_id;
END;
However when I try to execute it, it gives me this error message:
"Table 'db.m_sequence' doesn't exist"
Meaning that a table literally called m_sequence was not found, but i
would like to use the table inside
the m_sequence variable instead.
Anyone knows if it's possible and how?
Thank you very much
Stefano Locati - Obliquid
http://software.obliquid.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
