MySQL Stored Procedure Setting Variable

Question

I'm new to stored procedures in MySQL

I wonder if for example I have a query then I divide it into variables like

SELECT A, B, C, FROM TABLE123 WHERE A = '123'

Then I divide it into variables inside the stored procedure

SET @select = 'SELECT';
SET @column1= 'A';
SET @column2 = 'B';
SET @column3 = 'C';
SET @parameter = 'WHERE';
SET @equal = '=';

Then from there I can use each part or the query or variable in a conditional statement depending on the parameter set when calling the stored procedure.

I wonder if this is allowed as standard practice or will it cause any performance issue.

KaeL · Accepted Answer · 2016-05-19 01:42:26Z

1

I believe this is similar to Dynamic SQL.

Here is a basic how-to base from MySQL Docs:

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;

+------------+
| hypotenuse |
+------------+
|          5 |
+------------+

Here's a great article written by an SQL Server MVP about The Curse and Blessings of Dynamic SQL, which you might want to read. Although this is for sql server, there might be some similar information for mysql.

edited May 19, 2016 at 1:42

answered May 18, 2016 at 11:04

KaeL

3,6612 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Stored Procedure Setting Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related