I'm creating a stored procedure for MySQL in a C# app. The stored procedure contains some dynamically created SQL used to drop an index if it exists, then [re]create it. The SQL code works fine in MySQL Workbench, but when I run it from my C# app, I'm getting a MySQL Exception.
Here's the stored procedure code (as it is used in C#, there are no "@" symbols before identifiers as C# puts them in itself, as I understand it):
CREATE PROCEDURE recreate_index_sp(IN theTable varchar(128), IN theIndexName varchar(128), IN theIndexColumns varchar(128))
BEGIN
IF ((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name = theTable AND index_name = theIndexName) > 0) THEN
SET s = CONCAT( 'DROP INDEX ', theIndexName, ' ON ', theTable );
PREPARE stmt FROM s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
SET s = CONCAT('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
PREPARE stmt FROM s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
The exception I'm getting is: MySql.Data.MySqlClient.MySqlException: Unknown system variable 's'.
If I therefore define s at the start of the stored procedure - DECLARE s varchar(255) - I instead get the error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's;EXECUTE stmt;DEALLOCATE PREPARE stmt;END' at line 1. But the code I use in MySQL Workbench doesn't need to define the variable, so I don't know why it's insisting on it here.
If I use @s as the variable name instead (including defining it!), the exception says Parameter '@s' must be defined.
Any ideas how I can get this to work?
For info, here is the code I use in MySQL Workbench that creates the procedure quite happily:
CREATE PROCEDURE recreate_index_sp(IN theTable varchar(128), IN theIndexName varchar(128), IN theIndexColumns varchar(128))
BEGIN
IF ((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name = @theTable AND index_name = @theIndexName) > 0) THEN
SET @s = CONCAT( 'DROP INDEX ', @theIndexName, ' ON ', @theTable );
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
SET @s = CONCAT('CREATE INDEX ', @theIndexName, ' ON ', @theTable, '(', @theIndexColumns, ')');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
EDIT: Thanks to Reubz, I got the code working. Two parts were needed: (1) The connection string needed "Allow User Variables=True;" including, and (2) the @ symbol was required (and the variable did not need declaring).
So here's the working code:
CREATE PROCEDURE recreate_index_sp(IN theTable varchar(128), IN theIndexName varchar(128), IN theIndexColumns varchar(128))
BEGIN
IF ((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name = theTable AND index_name = theIndexName) > 0) THEN
SET @sqlPrep = CONCAT( 'DROP INDEX ', theIndexName, ' ON ', theTable );
PREPARE stmt FROM @sqlPrep;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
SET @sqlPrep = CONCAT('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
PREPARE stmt FROM @sqlPrep;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
