using variables as strings in stored procedure

Question

How do I use variables to represent string values in MySQL? The stored procedure below has two variables @varname and @file1. It also uses concat() to create a string from @file1, but none of these string values is populating properly into the script. (The query is not loading results and the outfile is not being created.) How can I fix this code so that it works?

DELIMITER $$

DROP PROCEDURE IF EXISTS `parse_descriptions`$$
CREATE PROCEDURE `parse_descriptions`()
BEGIN

SET @varname='something';
SET @file1= concat('D:/mypath/','@varname');

select d.id, d.term, d.conceptid from dtablename as d 
where term like '%@varname%' 
into outfile concat('@file1','_test.txt') 
fields terminated by '\t'
optionally enclosed by ""
lines terminated by '\r\n'
;

END$$

CALL `parse_descriptions`()$$

DROP PROCEDURE IF EXISTS `parse_descriptions`$$

You should just say it like SET @file1= concat('D:/mypath/',@varname); by removing the quote around the parameter. — Rahul
– Rahul, Commented Jun 19, 2014 at 19:45

Rahul · Accepted Answer · 2014-06-19 21:22:03Z

2

other than misplacing quotes in few places around the parameters as I have already commented the biggest problem is you are trying to use variable in select ...into outfile command which can only be done through prepared statement. A sample included below [tested and it works fine]. You can make the changes accordingly per your needs.

DELIMITER $$
CREATE PROCEDURE `parse_descriptions`()
BEGIN
SET @varname='harikas';
SET @file1= concat('D:/SO_Test/',@varname, '_test.txt');

SET @sql = CONCAT('select * from teachers where username like ''%', @varname, '%'' into outfile ''', @file1 , '''');
select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

Prepared statement going to generate a query like below

select * from teachers where username like '%harikas%' into outfile 
'D:/SO_Test/harikas_test.txt'

Call the procedure

call parse_descriptions()

EDIT: You are missing a ' at end of the line. Your modified procedure should looks like

DELIMITER $$
DROP PROCEDURE IF EXISTS `parse_descriptions`$$
CREATE PROCEDURE `parse_descriptions`()
BEGIN

SET @varname='something';
SET @file1= concat('D:/mypath/',@varname, '_test.txt');

SET @sql = concat('select d.id, d.term, d.cid from dtablename as d where term like ''%', @varname, '%'' into outfile ''', @file1 , ''' fields terminated by ''\t'' optionally enclosed by "" lines terminated by ''\r\n''');
select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

edited Jun 19, 2014 at 21:22

answered Jun 19, 2014 at 20:19

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

CodeMed Over a year ago

It is working now, so I will mark this as the answer.

Rahul Over a year ago

@CodeMed, modified your procedure. take a look in edited answer.

CodeMed Over a year ago

Thank you again. It takes a very long time to run the procedure, though the select statement itself takes only a few seconds when run in raw form. Why is this approach so slow?

Rahul Over a year ago

@CodeMed, should it not be? there are 3 operations involved 1. fetching data through select query -> 2. prepare the statement and 3. write the resultant data to disk file (the slowest one probably depends upon number of rows to write to disk file).

CodeMed Over a year ago

There are only 2 rows in the test output file but it takes 7 minutes to run the procedure. When I do the same thing without a procedure or variables, it all happens in less than 30 seconds using the same small test query.

|

SQLChao · Accepted Answer · 2014-06-19 19:49:01Z

1

Modified your code. Think you had the quotes wrong in a couple places

SET @varname='something';
SET @file1= concat('D:/mypath/',@varname);   --here

select d.id, d.term, d.conceptid from dtablename as d 
where term like concat('%', @varname, '%') -- here
into outfile concat(@file1,'_test.txt')  -- here
fields terminated by '\t'
optionally enclosed by ""
lines terminated by '\r\n'
;

answered Jun 19, 2014 at 19:49

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Collectives™ on Stack Overflow

using variables as strings in stored procedure

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related