Somethinghitme

I have been using MSSQL with ASP.NET alot lately, and I really wanted to get some of the benefits of stored procedures into a PHP application I'm writing. I've known MySQL has supported them for a while but I guess I was always a bit too lazy to go through old code and convert it. I used the tool HeidiSQL to make the process a bit more painless.

The first stored procedure I attempted to write accepted 3 parameters and based on one of them checks a lookup table, and creates a new value in it if the same value does not already exist. Normally I would use an IF NOT EXISTS clause, which in Microsoft SQL would look like this

CREATE PROCEDURE someProc
@SomeParam varchar(50),
AS
BEGIN
    DECLARE @SomeId int

    IF  NOT EXISTS(SELECT (1) FROM lookup_table WHERE someField = @SomeParam )
        BEGIN
            INSERT INTO lookup_table (someField) VALUES(@someParam)
            SELECT @SomeId = @@identity
        END
    ELSE
        BEGIN
            SELECT @SomeId = id FROM lookup_table WHERE someField = @SomeParam
        END

    -- Do some other stuff here with the id you just received and other data
END

The above snippet would take a value, check a table for it, get its id, and if the record doesn't exist in the database create it and return the id. Then you could continue on and insert that id into another table or what have you.

Accomplishing the same thing with a MySQL stored procedure is a bit different. The code below shows how you would accomplish the same task.

CREATE PROCEDURE 'someProc' (IN 'in_SomeParam' INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    DECLARE SomeId int;
    DECLARE CheckExists int;
    SET CheckExists = 0;

    SELECT count(*) INTO CheckExists from lookup_table WHERE someField = in_SomeParam;

    IF (CheckExists > 0) THEN
        SELECT id INTO SomeId FROM lookup_table WHERE someField = in_SomeParam;
    ELSE
        INSERT INTO lookup_table (someField ) VALUES(in_SomeParam);
        SELECT SomeId = LAST_INSERT_ID();
    END IF;

    # Do some other stuff here with the id you just received and other data
END;

I'm sure I will find some more differences in things I am used to doing with stored procedures using T-SQL, and when I do I'll post them here.