Description:
request to support dynamic sql statement execution in upcoming MySQL version 5.x
for example:
the codes below forms a dynamic sql statement depending on the passin parameters. this is just one of the many possibilities with dynamic sql statement within stored procedure. although this sort of process can be accomplished by issuing codes from the client side, there are situations where one does not want the client to know about the architecture of the database. with dynamic sql statement, one can even create dynamic stored procedures or anything on the fly! we did this with MS SQL server when there was such a requirement. a data structure with multi foreign fields and these records belong to a certain class where all members within the class is to be queried. the number of fields were dynamic and have to be flattened from row wise to column wise in a new table generated dynamically.
thanks.
kmhui
create procedure update_tbl
@nm varchar(50),
@address varchar(50)
as
begin
declare @bracket int /* use as flag */
set @bracket = 0
set @sql = 'UPDATE particulars ';
set @values = 'values ('
if len(@nm)>0
begin
set @bracket = 1
set @sql = @sql + '(' + 'nm'
set @values = @values + @nm
end
if len(@address) > 0
begin
if @bracket = 1
begin
set @sql = @sql + ',' + 'address' + ') '
set @values = @values + ',' + @address + ')'
end
else
begin
set @sql = @sql + '( address ) '
set @values = @values + @address + ')'
end
end
set @sql = @sql + @values + ';'
execute (@sql)
end
How to repeat:
na
Suggested fix:
na
Description: request to support dynamic sql statement execution in upcoming MySQL version 5.x for example: the codes below forms a dynamic sql statement depending on the passin parameters. this is just one of the many possibilities with dynamic sql statement within stored procedure. although this sort of process can be accomplished by issuing codes from the client side, there are situations where one does not want the client to know about the architecture of the database. with dynamic sql statement, one can even create dynamic stored procedures or anything on the fly! we did this with MS SQL server when there was such a requirement. a data structure with multi foreign fields and these records belong to a certain class where all members within the class is to be queried. the number of fields were dynamic and have to be flattened from row wise to column wise in a new table generated dynamically. thanks. kmhui create procedure update_tbl @nm varchar(50), @address varchar(50) as begin declare @bracket int /* use as flag */ set @bracket = 0 set @sql = 'UPDATE particulars '; set @values = 'values (' if len(@nm)>0 begin set @bracket = 1 set @sql = @sql + '(' + 'nm' set @values = @values + @nm end if len(@address) > 0 begin if @bracket = 1 begin set @sql = @sql + ',' + 'address' + ') ' set @values = @values + ',' + @address + ')' end else begin set @sql = @sql + '( address ) ' set @values = @values + @address + ')' end end set @sql = @sql + @values + ';' execute (@sql) end How to repeat: na Suggested fix: na