0
CREATE PROCEDURE data1 (IN v_dt2 VARCHAR(256) , v_col2 VARCHAR(256),col2 VARCHAR(256))
BEGIN

 SELECT dt1 as datep, var1 as vars, 'new' as type 
 FROM samp 
 UNION all
 SELECT v_dt2, v_col2, 'col2' as type 
 FROM samp 
 ;

END //
DELIMITER ;

CALL data1 ("dt2","var2","old")

In the above call statement which are in brackets are columns names but in the output giving the parameters as a text not considering as column names... how to convert them as a column name in select statement after UNION ALL

Tunaki
  • 132,869
  • 46
  • 340
  • 423
suri5913
  • 59
  • 6
  • 1
    Please do not use an ALL-CAPS title, it is distracting and annoying. –  Oct 06 '15 at 10:35
  • The short answer is that you cannot user variables as column or table names – e4c5 Oct 06 '15 at 10:40
  • 1
    Possible duplicate of [mysql field name from variable](http://stackoverflow.com/questions/4428761/mysql-field-name-from-variable) – e4c5 Oct 06 '15 at 10:41

1 Answers1

1

Your procedure is returning constant values, not column names. To do what you want, you need to use dynamic SQL (prepare and exec):

CREATE PROCEDURE data1 (IN v_dt2 VARCHAR(256), v_col2 VARCHAR(256), col2 VARCHAR(256))
BEGIN
    set @s = '
SELECT dt1 as datep, var1 as vars, ''new'' as type 
FROM samp 
UNION all
SELECT @v_dt2, @v_col2, ''col2'' as type 
FROM samp ';

    set @s = replace(@s, '@v_dt2', v_dt2);
    set @s = replace(@s, '@v_col2', v_col2);
    set @s = replace(@s, '@col2', col2);

    prepare s from @s;
    exec s;

    deallocate prepare s;
END //
DELIMITER ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786