Use MySQL to implement Sp_executesql_MySQL of SQLServer

Source: Internet
Author: User
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 ';

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.