CODEWITHSUNDEEP
mysqlstored-procedures
AdamP_OH
AdamP_OH

Reputation: 55

MySQL Stored Procedure with a hyphen in value

I have a stored procedure that works, but when I pass a value with a hyphen in it, it errors.

I call my procedure with a value like call create('server-v01',555); and I get the following error:

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 '-v01' at line 1

My procedure is as follows:

DELIMITER $$
CREATE PROCEDURE ct(tname varchar(20), sessionsnum INT(11))
BEGIN
DECLARE maxnum INT;
SET @s = CONCAT('INSERT INTO sessions_poll (server_name,sessions_num)                VALUES(''',tname,''',',sessionsnum,')');
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('DROP TABLE IF EXISTS ', tname);
PREPARE stm FROM @s;
EXECUTE stm;
SET @s = CONCAT('CREATE TABLE ', tname, ' (num INT, max INT)');
PREPARE stm FROM @s;
EXECUTE stm;
SELECT @maxnum:=max(sessions_num) INTO maxnum FROM sessions_poll WHERE server_name=tname AND DATE(ts)=CURDATE();
SET @s = CONCAT('INSERT INTO ', tname, ' (num,max) VALUES (', sessionsnum,',',maxnum,')');
PREPARE stm FROM @s;
EXECUTE stm;
END $$
DELIMITER ;

My question is, how can I handle a variable with a hyphen in it?

Upvotes: 0

Views: 1855

Answers (1)

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143081

Your question is not how to handle variable with a dash, but how to handle a table with a dash. Your procedure tries to create a table with a name specified in tname. To create (or drop) a table like this you need to quote it with backticks.

DROP TABLE IF EXISTS `server-01`;

In particular you need to

SET @s = CONCAT('DROP TABLE IF EXISTS `', tname, '`');

and the same for other instances.

Whether this is what you really want to do is a question, though ;-)

Upvotes: 2

Related Questions