c# - Using dynamic SQL in stored procedure? -

i'm creating stored procedure mysql in c# app. stored procedure contains dynamically created sql used drop index if exists, [re]create it. sql code works fine in mysql workbench, when run c# app, i'm getting mysql exception.

here's stored procedure code (as used in c#, there no "@" symbols before identifiers c# puts them in itself, understand it):

create procedure recreate_index_sp(in thetable varchar(128), in theindexname varchar(128), in theindexcolumns varchar(128))  begin      if ((select count(*) index_exists information_schema.statistics table_schema = database() , table_name = thetable , index_name = theindexname) > 0)          set s = concat( 'drop index ', theindexname, ' on ', thetable );         prepare stmt s;         execute stmt;         deallocate prepare stmt;     end if;     set s = concat('create index ' , theindexname , ' on ' , thetable, '(', theindexcolumns, ')');     prepare stmt s;     execute stmt;     deallocate prepare stmt; end; 

the exception i'm getting is: mysql.data.mysqlclient.mysqlexception: unknown system variable 's'.

if therefore define s @ start of stored procedure - declare s varchar(255) - instead error you have error in sql syntax; check manual corresponds mysql server version right syntax use near 's;execute stmt;deallocate prepare stmt;end' @ line 1. code use in mysql workbench doesn't need define variable, don't know why it's insisting on here.

if use @s variable name instead (including defining it!), exception says parameter '@s' must defined.

any ideas how can work?

for info, here code use in mysql workbench creates procedure quite happily:

create procedure recreate_index_sp(in thetable varchar(128), in theindexname varchar(128), in theindexcolumns varchar(128)) begin     if ((select count(*) index_exists information_schema.statistics table_schema = database() , table_name = @thetable , index_name = @theindexname) > 0)         set @s = concat( 'drop index ', @theindexname, ' on ', @thetable );         prepare stmt @s;         execute stmt;         deallocate prepare stmt;     end if;     set @s = concat('create index ', @theindexname, ' on ', @thetable, '(', @theindexcolumns, ')');     prepare stmt @s;     execute stmt;     deallocate prepare stmt; end 

---

edit: reubz, got code working. 2 parts needed: (1) connection string needed "allow user variables=true;" including, , (2) @ symbol required (and variable did not need declaring).

so here's working code:

create procedure recreate_index_sp(in thetable varchar(128), in theindexname varchar(128), in theindexcolumns varchar(128))  begin      if ((select count(*) index_exists information_schema.statistics table_schema = database() , table_name = thetable , index_name = theindexname) > 0)          set @sqlprep = concat( 'drop index ', theindexname, ' on ', thetable );         prepare stmt @sqlprep;         execute stmt;         deallocate prepare stmt;     end if;     set @sqlprep = concat('create index ' , theindexname , ' on ' , thetable, '(', theindexcolumns, ')');     prepare stmt @sqlprep;     execute stmt;     deallocate prepare stmt; end; 

as per our chat:

you have use @ symbol user defined variable s, in order differentiate system variable. however, user defined variables disabled default in newer versions of mysql connector. enable it, need make change connection string , add user variables=true parameter.