New SQL syntax sp_executesql_MySQL of MySQL5.0

The new SQL syntax sp_executesql of MySQL5.0 starts from MySQL 5.0 and supports a new SQL syntax:
PREPARE stmt_name FROM preparable_stmt;

EXECUTE stmt_name [USING @ var_name [, @ var_name]...];

{DEALLOCATE | DROP} PREPARE stmt_name;

Through it, we can implement sp_executesql similar to ms SQL to execute dynamic SQL statements!

It can also prevent injection attacks!

To have a perceptual knowledge,

Here are 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 later, you can use it in the LIMIT clause, for example: mysql> SET @ a = 1; mysql> prepare stmt from "SELECT * FROM tbl LIMIT? ";

Mysql> execute stmt using @;

Mysql> SET @ skip = 1; SET @ numrows = 5;

Mysql> prepare stmt from "SELECT * FROM tbl LIMIT ?, ? ";

Mysql> execute stmt using @ skip, @ numrows; notes for using prepare:

A: PREPARE stmt_name FROM preparable_stmt;

Predefines a statement and assigns it to stmt_name, which is case insensitive.

B: even in the preparable_stmt statement? It represents a string, and you do not need? Enclosed in quotation marks.

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

D: the scope of PREPARE stmt_name is visible to the current client connection session.

E: to release resources of a predefined statement, use the deallocate prepare syntax.

F: In the EXECUTE stmt_name syntax, if stmt_name does not exist, an error is thrown.

G: If the deallocate prepare syntax is not explicitly called to release the resource when terminating the client connection session, the server will manually release the resource.

H: in predefined statements, create table, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW syntaxes are supported.

G: The PREPARE statement cannot be used in stored procedures. it is a custom function! However, MySQL 5.0.13 and later versions can be used in stored procedures and cannot be used in functions! 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 ';