Using MySQL to implement SQL Server sp_executesql

Starting with MySQL 5.0, a new SQL syntax is supported:

PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;

Through it, we can implement a dynamic SQL statement like Ms SQL sp_executesql!

It can also prevent injection attacks!

To have a perceptual understanding, let's give you a few small examples:

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+--------- ---+
mysql> DEALLOCATE PREPARE stmt1;
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+----------- -+
| hypotenuse |
+------------+
| 10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

If your MySQL version is 5.0.7 or higher, you can also use it in the LIMIT clause, as shown in the following example:

mysql> SET @a=1;mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ? ";
mysql> EXECUTE STMT USING @a;
mysql> SET @skip=1; SET @numrows=5;
mysql> PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?, ?";
mysql> EXECUTE STMT USING @skip, @numrows;

Several points of attention using PREPARE:

A:prepare stmt_name from preparable_stmt;

Predefined a statement and assigns it to Stmt_name, Stmt_name is case-insensitive.

B: Even in the PREPARABLE_STMT statement? is represented by a string, and you do not need to be? Enclosed in quotes.

C: If the new PREPARE statement uses an existing stmt_name, then the original will be released immediately! Even if the new PREPARE statement cannot be executed correctly because of an error.

The scope of the D:prepare Stmt_name is that the current client connection session is visible.

E: To release a resource for a predefined statement, you can use the deallocate PREPARE syntax.

F:execute stmt_name syntax, if Stmt_name does not exist, an error will be raised.

G: If the client connection session is terminated without explicitly invoking the deallocate PREPARE syntax to release the resource, the server side will release it itself.

H: In predefined statements, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most show syntax are supported.

I:prepare statements cannot be used for stored procedures, custom functions! But starting with MySQL 5.0.13, it can be used for stored procedures and is still not supported in functions! Here's an example:

CREATE PROCEDURE `p1`(IN id INT UNSIGNED,IN name VARCHAR(11))BEGIN lable_exit: BEGIN SET @SqlCmd = 'SELECT * FROM tA ';
IF id IS NOT NULL THEN SET @SqlCmd = CONCAT (@SqlCmd , 'WHERE id=?');
PREPARE stmt FROM @SqlCmd;
SET @a = id;
EXECUTE stmt USING @a;
LEAVE lable_exit;
END IF;
IF name IS NOT NULL THEN SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE name LIKE ?');
PREPARE stmt FROM @SqlCmd;
SET @a = CONCAT(name, '%');
EXECUTE stmt USING @a;
LEAVE lable_exit;
END IF;
END lable_exit;
END;
CALL `p1`(1,NULL);
CALL `p1`(NULL,'QQ');
DROP PROCEDURE `p1`;