Which variable to use?

Databases
1

I have a confusion regarding defining variables in stored procedure . We can define variable in store procedure in two different ways …

DECLARE var1

SET @var1

I am confused because …

(a)why there are two ways to define a variable ?
(b)I’m not clear when to use DECLARE and when to use SET . Can you please explain the difference ? Is there any thumb rule when to use which variable ?

2

Is this MySQL? In MySQL the variable var1 and @var1 are two different things. @var1 is a user session variable which remains for the duration of the active session. The other is local to the stored procedure. In MySQL you need to use SET, SELECT … INTO var or FETCH … INTO for setting local or session variables.

3

Yes.

In MySQL the variable var1 and @var1 are two different things. @var1 is a user session variable which remains for the duration of the active session.

How do I set a user session variable ?

SET @var1 :=1;

SET @var1 =1;

are both of these syntax correct ?

4

Yes both are correct.

5

I have some doubt in this statement…

a store procedure can have multiple BEGIN END block.

I guess by local you mean the var is accessible in the block where it has been defined…whereas a user session variable can be accessible in all blocks irrespective of where it has been defined.

Please correct me if I’m wrong. I hope I understood you correctly.

6

Much better put than my post. When I say local it is local to the BEGIN/END block in which it is declared. For example:

BEGIN
DECLARE x INT DEFAULT 9;
SET @x := 10;
END;

x is only available inside this BEGIN/END block. However, @x is available and accessible anywhere within the session.

7

Can you please post an example illustrating red part ? I find that part bit confusing

8

Sure,


mysql> delimiter $$
mysql> create procedure demo(in pValue integer)
    -> sql security invoker
    -> no sql
    -> begin
    ->   set @x := pValue;
    -> end;
    -> $$
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> set @x := 10;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
| 10   | 
+------+
1 row in set (0.00 sec)

mysql> call demo(5);
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
| 5    | 
+------+
1 row in set (0.00 sec)
9

OK. I see your example. Thanks

That looks ok. I guess we may not use variables outside the store proc like the way you have posted.

idea is to to call a store proc and get the result …so declare var will be just fine for my purpose.

its good to know.

Thanks for the help.