CODEWITHSUNDEEP
mysql
suri5913
suri5913

Reputation: 59

Passing column names as a parameters and calling

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

Upvotes: 0

Views: 150

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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 ;

Upvotes: 1

Related Questions