mysql - using variables as strings in stored procedure -

mysql - using variables as strings in stored procedure -

how utilize variables represent string values in mysql? stored procedure below has 2 variables @varname , @file1. uses concat() create string @file1, none of these string values populating script. (the query not loading results , outfile not beingness created.) how can prepare code 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 dtablename d term '%@varname%' outfile concat('@file1','_test.txt') fields terminated '\t' optionally enclosed "" lines terminated '\r\n' ; end$$ phone call `parse_descriptions`()$$ drop procedure if exists `parse_descriptions`$$

other misplacing quotes in few places around parameters have commented biggest problem trying utilize variable in select ...into outfile command can done through prepared statement. sample included below [tested , works fine]. can create changes accordingly per needs.

delimiter $$ create procedure `parse_descriptions`() begin set @varname='harikas'; set @file1= concat('d:/so_test/',@varname, '_test.txt'); set @sql = concat('select * teachers username ''%', @varname, '%'' outfile ''', @file1 , ''''); select @sql; prepare stmt @sql; execute stmt; deallocate prepare stmt; end$$

prepared statement going generate query below

select * teachers username '%harikas%' outfile 'd:/so_test/harikas_test.txt'

call procedure

call parse_descriptions()

edit: missing ' @ end of line. 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 dtablename d term ''%', @varname, '%'' outfile ''', @file1 , ''' fields terminated ''\t'' optionally enclosed "" lines terminated ''\r\n'''); select @sql; prepare stmt @sql; execute stmt; deallocate prepare stmt; end$$

mysql sql