SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    which variable to use ?

    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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    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 ?

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes both are correct.

  5. #5
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have some doubt in this statement...

    Quote Originally Posted by rcashell View Post
    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.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    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

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sure,

    Code:
    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. #9
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    302
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •