mysql - Removing dependence on dynamic SQL through Ant scripting -

mysql - Removing dependence on dynamic SQL through Ant scripting -

i've been given responsibility on number of stored procedures, take form:

create procedure getfoos() begin set @v_sql := ''; set @v_sql := concat(@v_sql, 'select distinct ', getfoofields('f', 'b'), ' '); set @v_sql := concat(@v_sql, 'from foos f '); set @v_sql := concat(@v_sql, ' left bring together bars b on f.barid= b.barid'); set @v_sql := concat(@v_sql, 'where f.somedate null '); set @v_sql := concat(@v_sql, ' , b.someid in (1, 2, 3) '); set @v_sql := concat(@v_sql, ' , b.somebool = true '); set @v_sql := concat(@v_sql, 'order f.name '); prepare s1 @v_sql; execute s1; deallocate prepare s1; end;

as can see, dynamic sql beingness used function getfoofields can inlined. function used build string of selected fields:

create function getfoofields( i_aliasforfoo varchar(32), i_aliasforbar varchar(32) ) returns text reads sql info begin declare v_fields text default ''; set v_fields := concat(v_fields, i_aliasforfoo, '.fooid fooid', ', '); set v_fields := concat(v_fields, i_aliasforfoo, '.somedate somedate ', ', '); set v_fields := concat(v_fields, i_aliasforfoo, '.name name', ', '); -- additional foo fields set v_fields := concat(v_fields, i_aliasforbar, '.someid someid ', ', '); set v_fields := concat(v_fields, i_aliasforbar, '.somebool somebool ', ', '); -- additional bar fields homecoming v_fields; end;

the reason seems improve maintainability - when foos or bars tables altered, changes have made in 1 function instead of many procedures. coming @ cost of using dynamic sql, function builds same string every single time it's called. i'm wondering if there's way maintain maintainability while moving work before runtime.

as happens, managing our sql in eclipse , using ant massage these files before running them on database prior every deployment. there way script ant work of getfoofields instead? imagine following:

select distinct <%foofields%> foos ...

where ant replace <%foofields%> string similar getfoofields building. of course of study farther complicated fact getfoofields takes parameters i'm not sure how work.

is thought misguided? hardly have experience ant can't tell. alternatively, other ways remove these procedures' dependence on dynamic sql while keeping them maintainable?

if getfoofields used select fields table foo improve off using select * foo combination of dynamic sql , function.

if subset of fields in foo occur group, might improve served creating subset view , querying against view.

if getfoofields taking list of fields application select foo have examine whether or not makes sense. how many fields in foo? how need little number of fields? pulling big plenty number of records type of "optimization" meaningful? likely, aren't , improve served pulling mutual fields or of them.

mysql eclipse ant