CODEWITHSUNDEEP
mysqlstored-procedures
s_p
s_p

Reputation: 4693

Dynamic Table Name in If/Else clause MySQL prepared statement

I am trying to pass in a table name as variable along with some other variables and create an if else condition.

This is what I have so far but getting errors since my syntax is incorrect. Any help would be greatly appreciated. Thank you.

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
  IN tableName VARCHAR(50), 
  IN cartName VARCHAR(11)
)
BEGIN
SET @getTable1 =
  CONCAT("SELECT * FROM ", tableName, 
        " WHERE cartType='", cartName, "' AND deleted='1'");
PREPARE stmt1 FROM @getTable1;
EXECUTE stmt1;

IF EXISTS @getTable1 (do nothing)
ELSE
BEGIN
SET @getTable2 =
  CONCAT("UPDATE deleted FROM ", tableName, 
        " SET deleted='1'");
 PREPARE stmt2 FROM @getTable2;
EXECUTE stmt2;
END

END

Upvotes: 1

Views: 1647

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

Your IF syntax is way off, however, for this you do not even need it.

Just UPDATE the row, and check, if it is needed after that.

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE new_procedure(
  IN tableName VARCHAR(50), 
  IN cartName VARCHAR(11)
)
BEGIN
  PREPARE stmt FROM CONCAT('UPDATE deleted FROM ', tableName,
                           ' SET deleted = 1 WHERE cartType=''', cartName,
                           ''' AND deleted <> 1');
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

If you want to check whether an update is occurred, you can do this:

  -- Place this just after the EXECUTE statement:
  IF FOUND() THEN
    -- do your stuff if updated...
  ELSE
    -- do your stuff if not updated...
  END IF;

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562260

First of all, if you want to know if the first SELECT query returned any results, use SELECT COUNT(*) and store it in a user-defined variable. If you don't use SELECT...INTO, then it'll generate a result set and the stored procedure will return that.

SET @cartName = cartName;
SET @getTable1 =
  CONCAT("SELECT COUNT(*) INTO @deletedCount FROM `", 
        REPLACE(tableName, '`', '``'), 
        "` WHERE cartType=? AND deleted='1'");
PREPARE stmt1 FROM @getTable1;
EXECUTE stmt1 USING @cartName;

Also try to avoid SQL injection vulnerabilities. Use query parameters for values, and at least use backticks to delimit the table-name. Escape literal backtick characters in the tableName.

Once you have this result in the user-defined variable, check that variable to see if it's zero.

Review the syntax for UPDATE. There's no FROM keyword in an UPDATE statement. You don't name the column until the SET clause. When in doubt, check the syntax in the manual: https://dev.mysql.com/doc/refman/5.7/en/update.html

IF @deletedCount = 0 THEN
BEGIN
  SET @getTable2 =
    CONCAT("UPDATE `", 
        REPLACE(tableName, '`', '``'), 
        "` SET deleted='1'"
        -- WHERE...?
   ); 
   PREPARE stmt2 FROM @getTable2;
  EXECUTE stmt2;
END

Is there supposed to be a WHERE clause for the cartType in your UPDATE? Seems like there should be, otherwise you'll update all rows for all cart types. Nothing in the syntax you use makes the update apply only to the matching rows from the first SELECT. Each SQL statement is context-free (MySQL does not support updatable cursors).

Upvotes: 2

Related Questions