ITG Systems Integration Inc.

Data Architects

Tech Blog - Create code from data

This MySql function can be modified to create any kind of code that involves the use of a column/table/view name. Typically you would use it to create code for inputting or verifying data. The example below just creates a string with the names of the columns.

CREATE  FUNCTION `CreateVar`(_Usertype int, _TableName varchar(200)) RETURNS varchar(2000) CHARSET utf8
    DETERMINISTIC
begin

declare _cursordone INT default 0;
Declare _clist varchar(2000) ;
Declare _cname  varchar(200);
DECLARE getcol 
CURSOR FOR SELECT column_name FROM information_schema.columns where table_name = _TableName ;
declare continue handler for NOT FOUND set _cursordone = 1;
set _clist = '';
  OPEN getcol;
 repeat
    fetch getcol into _cname ;
    if not _cursordone then
        -- Modify to suite your needs
       if LENGTH(_clist) > 0 then
          set _clist =concat( _clist , ',') ;
       end if;
       set _clist = concat(_clist , '"', _cname,'"') ;
        -- Modify to suite your needs
    end if;
 until _cursordone end repeat;
 close getcol ;
Return _clist ;
end