MySQL Stored Procedure, Execute SQL Taken from Table using PREPARE/EXECUTE

Question

I'm having trouble using PREPARE and EXECUTE as I'm doing something a little odd - executing a pre-prepared SQL statement taken from a database (rather than building it in the Stored Procedure).

I saw the question MySQL :: Run SQL statement within Variable and this one My SQL Dynamic query execute and get ouput into a variable in stored procedure and from these gathered that I cannot use a SP DECLARED variable in the prepare statement (MySQL WorkBench gives an error as soon as I try) and so have to use a variable with 'AT symbol' (not sure what this means?). So it is I have been trying to get the following to work:

DECLARE sqlUpdateSp TEXT;

IF (SELECT EXISTS (SELECT updateSql FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn)) THEN
    SELECT updateSql INTO sqlUpdateSp FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn;  
    SET @phlanx = sqlUpdateSp;
    PREPARE sqlToExecute FROM @phlanx;
    EXECUTE sqlToExecute;
END IF

I get no errors and the rest of the SP functions fine but this SQL never gets executed, I'm guessing because 'sqlToExecute' isn't being formed properly.

Everything I can find on PREPARE/EXECUTE in SPs assumes that the SQL being executed is being created in the SP, not pulled from a DB.

As an aside, how does your `sys_instantUpdates` table get populated? Beware of SQL injection! — eggyal, Apr 23 '14 at 12:53
See also [MySQL: @variable vs. variable. Whats the difference?](http://stackoverflow.com/a/1010042) — eggyal, Apr 23 '14 at 12:58
@eggyal - thanks for the link! Fear not, the SQL statements are constructed start to finish by code without using any user input. This system essentially allows a website owner to mark a submitted comment as visible, hide it or delete it with a single click without logging in. Everything is encapsulated and seperated from the user input and combines a 32 character alphanumeric security token that expires within 24 hours too (although worse case scenario if a psychic were able to guess all the tokens, the worse that could happens would be visitor comments getting deleted). — Mr Fett, Apr 23 '14 at 13:06

score 0 · Answer 1 · answered Apr 23 '14 at 12:56

0

try to use stmt instead of sqlToExecute

DECLARE sqlUpdateSp TEXT;

IF (SELECT EXISTS (SELECT updateSql FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn)) THEN
    SELECT updateSql INTO sqlUpdateSp FROM sys_instantUpdates WHERE expiryDate >= NOW() AND securityToken = securityTokenIn AND updateType = updateTypeIn;  
    SET @phlanx = sqlUpdateSp;
    PREPARE stmt FROM @phlanx;
    EXECUTE stmt ;
END IF

answered Apr 23 '14 at 12:56

Ronak Shah

1,539
2
13
20

Thanks Ronak - I assumed that 'stmt' was just an arbitrary name - is that not the case? – Mr Fett Apr 23 '14 at 13:03
Ronak - the sqlToExecute (or stmt) is indeed an arbitrary name and can be whatever you want (so should be descriptive ideally). – Mr Fett Apr 23 '14 at 13:50

score 0 · Accepted Answer · answered Apr 23 '14 at 13:49

0

Apologies to all - this appears to have randomly started working. I'm not sure if during my tests MySQL was caching the results and so the update wasn't showing but not it's working fine with no changes to the code.

answered Apr 23 '14 at 13:49

Mr Fett

7,979
5
20
21

Can you mark this as accepted, so the questions doesn't show up in the unanswered question list? Thanks,David. – dsolimano May 01 '14 at 14:26

MySQL Stored Procedure, Execute SQL Taken from Table using PREPARE/EXECUTE

2 Answers2