CODEWITHSUNDEEP
mysql
Anuya
Anuya

Reputation: 8350

How to use AS keyword in MySql?

In the below SP i will be getting result in One single column. How can i name the column of the output ?

    DELIMITER $$

DROP PROCEDURE IF EXISTS `InsGen` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`
(
in_db varchar(20),
in_table varchar(20),
in_ColumnName varchar(20),
in_ColumnValue varchar(20)
)
BEGIN

declare Whrs varchar(500);
declare Sels varchar(500);
declare Inserts varchar(2000);
declare tablename varchar(20);
declare ColName varchar(20);

set tablename=in_table;


# Comma separated column names - used for Select
select group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')'))
INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename;


# Comma separated column names - used for Group By
select group_concat('`',column_name,'`')
INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename;


#Main Select Statement for fetching comma separated table values
set @Inserts=concat("select concat('insert into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');')
from ", in_db,".",tablename, " where ", in_ColumnName, " = " , in_ColumnValue, " group by ",@Whrs, ";");

PREPARE Inserts FROM @Inserts;

EXECUTE Inserts;

END $$

DELIMITER ;

Upvotes: 3

Views: 4109

Answers (3)

Anuya
Anuya

Reputation: 8350

This Worked for me : 

set @Inserts=concat("select concat('insert into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');') as MyColumn from ", in_db,".",tablename, " where ", in_ColumnName, " = " , in_ColumnValue, " group by ",@Whrs, ";");

Upvotes: 0

ceteras
ceteras

Reputation: 3378

set @Inserts=concat("select concat('insert into ", in_db,".",tablename," values(',concat_ws(',',",@Sels,"),');') as `Inserts` from ", in_db,".",tablename, " where ", in_ColumnName, " = " , in_ColumnValue, " group by ",@Whrs, ";");

PREPARE Inserts FROM @Inserts;

EXECUTE Inserts;

Add "AS" statement to the first two select statements as advised in the first answer.

Upvotes: 0

user47322
user47322

Reputation: 

SELECT something AS somename ...

In your case, it'd be:

SELECT group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')')) AS myColumnName
INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename;

Upvotes: 2

Related Questions