1

is there any method how to make MySQL function that receives table name or field name ?

something like this : 

CREATE PROCEDURE delete_row(the_id INT UNSIGNED , @table_name )  
BEGIN  
    IF ....... THEN
    BEGIN
        DELETE FROM @table_name WHERE id = the_id ;
        .............
    END
END

I tested it with string (SET @table_name="table_name"), but it doesn't works.

Dani-Br
  • 2,289
  • 5
  • 25
  • 32
  • I found helpful answer [here](http://stackoverflow.com/questions/2977356/in-mysql-how-to-pass-a-table-name-as-stored-procedure-and-or-function-argument). by this answer I creates a string of SQL statement and execute the string. can be serious problem of SQL injection. – Dani-Br Jun 22 '11 at 20:43

1 Answers1

-1
declare varchar(max) @mySQL
set @mySQL = 'DELETE FROM ' + @tablename + 'WHERE id = ' + Convert(varchar, @the_id)
sp_executeSQL @mySQL

and to make this work on MySQL (as the commenters pointed out) it should look like this:

mysql> prepare stmt from -> 'DELETE FROM ' + @tablename + 'WHERE id = ' + Convert(varchar, @the_id)
mysql> execute stmt;
therealmitchconnors
  • 2,732
  • 1
  • 18
  • 36
  • Edit: the above works in SQL Server. In MySQL I think it looks like `mysql> prepare stmt from -> 'DELETE FROM ' + @tablename + 'WHERE id = ' + Convert(varchar, @the_id) mysql> execute stmt;` – therealmitchconnors Jun 22 '11 at 16:38
  • that looks very SQL Server like :) Mitch, you comment should be your answer then. – p.campbell Jun 22 '11 at 16:39
  • The general idea of this answer is good (executing a prepared statement), but none of the code is even remotely going to work on MySQL. – Wouter van Nifterick Jun 22 '11 at 16:40
  • Fore more info on the MySQL implementation see [this post](http://rpbouman.blogspot.com/2005/11/mysql-5-prepared-statement-syntax-and.html) – therealmitchconnors Jun 22 '11 at 16:42
  • To start, statements need to be terminated, so at the end of each line you should have a semicolon. Then, in MySQL, you declare variables like `DECLARE `, so the variable name and the type are switched. Varchar(max) makes no sense in MySQL. The @ prefix is reserved for session variables, which you cannot "declare". Well, that's what's not working on the first line. What follows is equally full of incompatible code, but you get the picture. Your idea to use a prepared statement is a good one though. – Wouter van Nifterick Jun 22 '11 at 18:58
  • Yeah, I see that my first answer was completely SQLServer. But what about my comment on MySQL... did I get the syntax mostly right there? I am very new to MySQL, but I would appreciate any critiques. – therealmitchconnors Jun 22 '11 at 19:10