Use MySQL to implement Sp_executesql_MySQL of SQLServer

Using MySQL to implement Sp_executesql of SQLServer 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!

In order to have a perceptual knowledge, let's give 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.

I: 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! The following is 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 @;
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 @;
LEAVE lable_exit;
End if;
END lable_exit;
END;
CALL 'p1' (1, NULL );
CALL 'p1' (NULL, 'QQ ');
Drop procedure 'p1 ';