Silwerclaw
Silwerclaw

Reputation: 695

Dynamic variable names in MySQL

I need to create some stored function in MySQL that will work exactly like getter/setter and return or set for me variable by it's name.

For example get_variable('my_special') will return me value of @my_special variable, set_variable('my_special', 23) will set @my_special := 23.

Question is: how can I, having variable name as a string set or get it in MySQL? Smth like SET @{'my_special'} = 23 or $$var = 23 (as in PHP)

Update: According to my task I found that it's impossible to do in mysql. The purpose I wanted this was a chain of events:

Upvotes: 2

Views: 6600

Answers (2)

Dennis Y. Parygin
Dennis Y. Parygin

Reputation: 423

create procedure `eval`( in param text )                                                                                                                   
begin 
    set @sql = param; 
    prepare stmt from @sql; 
    execute stmt; 
    deallocate prepare stmt; 
end

Call the procedure call tests.eval('set @ABC = 120') and in the current session, you can access the variable @ABC

call tests.eval('set @ABC = 120');
select @ABC;

Source

Upvotes: 2

O. Jones
O. Jones

Reputation: 108839

If you must do this in SQL you're going to need the MySQL feature called Prepared Statements. Despite its name, this is not the same as the client-side prepare() feature offered by JDBC, mysqli, PDO, and other apis supporting client side languages.

Why? SQL doesn't allow the names of database objects (tables, columns, &c.) to be handled as bind variables.

Upvotes: 0

Related Questions