jeudi 24 mars 2016

Multiple queries in a MySQL Prepared Statement (without Procedure)

I have the following MySQL script and it's not working. When I deleted the second query (the SELECT) from the IF (after the INSERT INTO), it started to work. My question is: Can I execute multiple queries within a Prepared Statement but not to use a Procedure (and the last part is the reason why I opened a new question for this)?

Thanks in advance...

SQL:

SET @data1 = 'DATA_HERE' COLLATE utf8_general_ci
;

/* Random key */
SET @randomKey = CONCAT( 'rnd', DATE_FORMAT(NOW(), '%Y%m%d') , FLOOR( RAND() * 100 ) ) COLLATE utf8_general_ci
;

/* If not exists do an INSERT else SELECT a warning message */
SET @stmt = IF(
        ( SELECT EXISTS (
                SELECT
                    t0.id
                FROM
                    table t0
                WHERE 1
                    AND t0.key = @randomKey COLLATE utf8_general_ci
                    AND t0.data = @data1 COLLATE utf8_general_ci
            )
        ) = 0
        , CONCAT( "
            INSERT INTO
                table
            SET
                key = @randomKey COLLATE utf8_general_ci
                , data = @data1 COLLATE utf8_general_ci;

            SELECT
                t0.*
            FROM
                table t0
            WHERE 1
                AND t0.id = LAST_INSERT_ID(); " 
        )
        , CONCAT( "SELECT CONCAT( 'This random key already exists with this data!' );" )
    )
;

PREPARE stmt FROM @stmt
;
EXECUTE stmt
;

Aucun commentaire:

Enregistrer un commentaire