Error in MySql Query Syntax

I am trying to create a MySql stored procedure through C#. I have created some other as well but when I try to execute the Query via MySqlCommand.ExecuteNonQuery its throws a exception that you query syntax is not compatible. Here is the exception 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 'SET VariableRecordExists = (SELECT COUNT(*) FROM SmartCache_Sync WHERE MachineNa' at line 10

And here I am trying to build the query string:

s开发者_StackOverflow社区tring sql = @"CREATE PROCEDURE SmartCache_UpdateSync
(
VariableMachineName varchar(50)
)
BEGIN
    DECLARE VariableRecordExists int;
    DECLARE VariableSetDate datetime;
    START TRANSACTION;
        SET VariableSetDate= Now()
        SET VariableRecordExists = (SELECT COUNT(*) FROM SmartCache_Sync WHERE MachineName = VariableMachineName)
        IF VariableRecordExists = 1
        THEN
            UPDATE SmartCache_Sync 
            SET LastUpdate = VariableSetDate
            WHERE MachineName= VariableMachineName;
        ELSE
            INSERT INTO SmartCache_Sync 
            (MachineName,LastUpdate)
            VALUES (VariableMachineName,VariableSetDate);
        END IF;
    COMMIT; 

    SELECT VariableSetDate;
END";

I don't know where I am making a mistake. Probably I am missing a semi colon ; somewhere or what. I would be obliged if anyone could help me.

I don't know about making queryies in c#, but normally the ; is the end of your query: so if you don't change the delimiter, you are ending your command early. normally you would do something like this:

delimiter //

CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
 END//

delimiter ;

from: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Slightly off-topic - but...

In general, prefer "if exists (select...)" to "select count(*)..." when all you want to do is check if any rows exist. It is far cheaper than actually counting all the rows.

And secondly, it looks as though you are trying to do an "upsert", which in MySQL would be

INSERT INTO SmartCache_Sync(MachineName,LastUpdate)
    VALUES (VariableMachineName,VariableSetDate)
    ON DUPLICATE KEY UPDATE LastUpdate = VariableSetDate

and then you don't need the explicit transaction either.

This of course assumes that MachineName is a primary key, which I'm guessing it is.

My guess was right I was missing a ";". And honestly speaking it took me 2 hours to that out.

**

SET VariableSetDate= Now(); SET VariableRecordExists = (SELECT COUNT(*) FROM SmartCache_Sync WHERE MachineName = VariableMachineName);

**

Both the statements didn't have ";" at the end

CREATE PROCEDURE SmartCache_UpdateSync
 (
    VariableMachineName varchar(50)
)
    BEGIN
        DECLARE VariableRecordExists int;
        DECLARE VariableSetDate datetime;
        START TRANSACTION;
            SET VariableSetDate= Now()            

(SELECT VariableRecordExists = COUNT(*) FROM SmartCache_Sync WHERE MachineName = VariableMachineName)


            IF VariableRecordExists = 1
            THEN
                UPDATE SmartCache_Sync 
                SET LastUpdate = VariableSetDate
                WHERE MachineName= VariableMachineName;
            ELSE
                INSERT INTO SmartCache_Sync 
                (MachineName,LastUpdate)
                VALUES (VariableMachineName,VariableSetDate);
            END IF;
        COMMIT; 

        SELECT VariableSetDate;
    END";