MySQL IF NOT EXISTS

I would like to have a message come back from the stored procedure that tells whether or not the row was updated. I have a primary key of ID on the table, but it is auto-incremented, so it would not create a violation of the key constraint by inserting into the table since I am not specifying that column in the insert statement. I could create a unique key based upon the columns that need to be unique, but it seems that INSERT IGNORE would simply suppress insert errors, not tell me that the row already exists so I can give the user a chance to change and resubmit on the front end. Am I missing something?

For some reason, I'm having problems with what you suggested. I'm sure it's something minor, but I can't figure it out. Here is my code as it is now...

CREATE PROCEDURE INTRA_UpdateEmployee (
in_Action varchar(10),
in_ID int,
in_FirstName varchar(255),
in_LastName varchar(50),
in_Company int,
in_Department int,
in_Title varchar(255),
in_Supervisor varchar(1),
in_EmailAddress varchar(255),
in_OfficePhone varchar(15),
in_HomePhone varchar(15),
in_CellPhone varchar(15),
in_FaxNumber varchar(15),
in_IMName varchar(50)
)

sp:BEGIN

      DECLARE @existCount int;

      IF in_Action = 'insert'
    SET @existCount = 0;
        Select @existCount := Count(*) From employees Where ID = in_ID;

            IF @existCount > 0

                  Insert Into employees (ID, Name, Password, InActive, Extension)
                  Values (in_Action, in_ID, in_FirstName, in_LastName, in_Company, in_Department, in_Title, in_Supervisor, in_EmailAddress, in_OfficePhone, in_HomePhone, in_CellPhone, in_FaxNumber, in_IMName);

                  Select 'Insert Successful.' as Message;
                  leave sp;
            ELSE
                  Select 'There is already an employee with this name.' as Message;
                  leave sp;
            END IF
      END
      ELSEIF (in_Action = 'update')
        SET @existCount = 0;
        Select @existCount := Count(*) From employees Where ID = in_ID;

            IF @existCount = 0

                  Update employees
                  Set firstname = in_FirstName,
                        lastname = in_LastName,
                        company = in_Company,
                        department in_Department,
                        title = in_Title,
                        supervisor = in_Supervisor,
                        emailaddress = in_EmailAddress,
                        officephone = in_OfficePhone,
                        homephone = in_HomePhone,
                        cellphone = in_CellPhone,
                        faxnumber = in_FaxNumber,
                        imname = in_IMName
                  Where ID = in_ID;

                  Select 'Update Successful.' as Message;
                  leave sp;
            END IF

      ELSE
            Select 'Invalid Action.' as Message
            leave sp;
      END IF

END

I'm getting:

#1064 - 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 '@existCount int;

    IF in_Action = 'insert'
    SET @existCount = 0;
      Selec' at line 20

which Version of Mysql are we dealing with?

MySql is very dependant on versions as to the scope of the sql syntax that can be utilised....

in general you should attempt to change the processing to

insert into mytable
    (column list)
   select @col1,@col2 , .... values
       from AsingleRowTable
      Where @id not in (select id from mytable)

Update mytable
   set col1=@col1,Col2=@col2
  where id=@id



 

Lowfatspread, which part of the code are you referring to? Are you saying that I shouldn't use the input variables in my insert statements?