Why dynamic sql with stored procedure for connecting to database use User-Defined Variables

Question

I searching about dynamic sql and stored procedure for connneting to database.

All example use User-Defined Variables. like this How To have Dynamic SQL in MySQL Stored Procedure(How To have Dynamic SQL in MySQL Stored Procedure) (The second one.)

why?

Why do we have to hold there variables over statement　for just pick up data from database in procedure.

which make me think another name of variable everytime we make procedure that for pick data.

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another. from https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

Because User-Defined Variables cause following.

CREATE PROCEDURE prc_test ()
BEGIN
    DECLARE var2 INT DEFAULT 1;
    SET var2 = var2 + 1;
    SET @var2 = @var2 + 1;
    SELECT  var2, @var2;
END;

SET @var2 = 1;

CALL prc_test();

var2  @var2
---   ---
2     2


CALL prc_test();

var2  @var2
---   ---
2     3


CALL prc_test();

var2  @var2
---   ---
2     4

from MySQL: @variable vs. variable. What's the difference?

Please try to rephrase the question - as is it is unclear. And why do you refer to dynamic sql in the title and not use it in the procedure? — P.Salmon, Sep 10 '20 at 05:52

score 0 · Answer 1 · answered Sep 11 '20 at 07:59

I missed this passage in mysql official site. https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html

Because local variables are in scope only during stored program execution, references to 
them are not permitted in prepared statements created within a stored program.
Prepared statement scope is the current session, not the stored program, so the statement 
could be executed after the program ends, at which point the variables would no longer be
in scope. 
For example, SELECT ... INTO local_var cannot be used as a prepared statement.
This restriction also applies to stored procedure and function parameters. See Section 
13.5.1, “PREPARE Statement”.

Why dynamic sql with stored procedure for connecting to database use User-Defined Variables

1 Answers1