Creating a Stored Procedure in MySQL with C#
c#, mysql, stored-procedures
Solution
Remove `DELIMITER $$` at the beginning and `$$` after last `END`
`DELIMITER` is a mysql client command that enables you to change its statement terminator temporarily while you define a stored routine.
If you are defining a stored routine from within a programming interface that does not use the semicolon as a statement terminator, semicolons within stored routine definitions do not present any special issues.
Problem
I'm trying to create a new stored procedure programatically using the following code: ``` using (MySqlConnection conn = new MySqlConnection(connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { using (MySqlCommand command = conn.CreateCommand()) { command.CommandText = query; command.ExecuteNonQuery(); } trans.Commit(); } } ``` And the following text as the create statement copied from Mysql workbench: ``` static string query = @" delimiter $$ CREATE PROCEDURE `GetParentIds`(IN `tempTableName` VARCHAR(255), IN `id` int) BEGIN DECLARE parId INT; DECLARE curId INT; DROP TEMPORARY TABLE IF EXISTS tempTableName; CREATE TEMPORARY TABLE tempTableName ( node_id INT NOT NULL PRIMARY KEY ); set curId := id; get_parents_loop: LOOP set parId := null; set parId = (select ParentID from {TableName} where ID = curId); IF parId is NULL THEN LEAVE get_parents_loop; END IF; INSERT INTO tempTableName(node_id) Values (parId); set curId := parId; END LOOP get_parents_loop; SELECT * FROM tempTableName; END$$"; ``` This procedure is passed an ID of an object which has a parentID and it gets all of the parentIDs of all the parents of the given object and returns them. The problem comes when I try to run it and I get the following message: "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 'delimiter $$ CREATE PROCEDURE `GetParentIds`(IN `tempTableName` VARCHAR(255),' at line 1" Any and all Ideas are welcome! * EDIT ** Thanks to all the answers below, this is what finally worked: ``` CREATE PROCEDURE GetParentIds(IN tempTableName VARCHAR(255), IN id int) BEGIN DECLARE parId INT; DECLARE curId INT; DROP TEMPORARY TABLE IF EXISTS tempTableName; CREATE TEMPORARY TABLE tempTableName (node_id INT NOT NULL PRIMARY KEY ); set curId := id; get_parents_loop: LOOP set parId := null; set parId = (select ParentID from TDOs where TDOID = curId); IF parId is NULL THEN LEAVE get_parents_loop; END IF; INSERT INTO tempTableName(node_id) Values (parId); set curId := parId; END LOOP get_parents_loop; SELECT * FROM tempTableName; END; ```