CODEWITHSUNDEEP
mysqlsql
CodeMed
CodeMed

Reputation: 9181

using variables as strings in stored procedure

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`$$

Upvotes: 1

Views: 1059

Answers (2)

Rahul
Rahul

Reputation: 77866

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$$

Upvotes: 2

SQLChao
SQLChao

Reputation: 7847

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'
;

Upvotes: 1

Related Questions