Tuesday, October 28, 2014

How to use dynamic sql and use its return value



SET @sql = NULL;
SET @cout = '';
SELECT CONCAT('SELECT CONCAT(',GROUP_CONCAT(c.COLUMN_NAME, ',","'),') as v1 INTO @cout FROM 
   student where enrolment_month =given_month() and enrolment_year=given_year()')
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME IN ('client_group_types') ;
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;
DEALLOCATE PREPARE sql_statement; 
SET @Param2 = @cout;
SELECT @Param2;

Can use @Param2 as return type of a function