CODEWITHSUNDEEP
mysqlstored-procedures
toink
toink

Reputation: 255

MySQL Stored Procedure Setting Variable

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.

Upvotes: 0

Views: 648

Answers (1)

KaeL
KaeL

Reputation: 3659

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.

Upvotes: 1

Related Questions