which variable to use ?

[windowsxp]

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 ?

[rcashell]

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.

[windowsxp]

Is this MySQL?

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 ?

[rcashell]

Yes both are correct.

[windowsxp]

I have some doubt in this statement...

The other is local to the stored procedure.

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.

[rcashell]

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.

[windowsxp]

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.

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

[rcashell]

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)

[windowsxp]

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.