0

Possible Duplicate:
MySQL: @variable vs. variable. Whats the difference?

I am coming from frontend background so bear with me if this question seems elementary.

I noticed the majority of posts on MySQL stored proc uses @ for variables. I know this means the variable is a session variable that across stored proc will persist as long as the connection is alive but why would you want one variable to cross-contaminate multiple stored proc calls within 1 connection? Isn't it better to localize everything? If I don't use @, the variable is re-initialized to NULL on the next stored proc call, isn't that more desirable? Is this done for performance reason?

Community
  • 1
  • 1
pixelfreak
  • 17,714
  • 12
  • 90
  • 109
  • `why would you want one variable to cross-contaminate multiple stored proc calls within 1 connection` I did not get that. How does adding a variable do that? – Ibu Nov 27 '12 at 00:30
  • 1
    @Ibu What I meant is this, if in one stored proc, you do SET `@foo = "bar"`, then on the next stored proc in the same connection, `@foo` will still be set to "bar" – pixelfreak Nov 27 '12 at 00:36

1 Answers1

1

The @ symbol designates a User-Defined Variable. It seems like you've already read the manual entry on this, but I'll post this excerpt anyway.

You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another. User-defined variables are connection-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client connection are automatically freed when that client exits.

If you have multiple stored procedure calls within a single connection, you should be very aware of what variables are used when and if there is any redefining of a variable's value being done throughout the request. Also, you should be using a Transaction under certain conditions which also helps to make sure UDVs defined within are not corrupted from outside sources through the life of the request.

Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • 1
    Thanks, but this doesn't tell me why I should or should not use `@` in my variable except because the docs say so? If I don't use `@`, the variable is re-initialized to `NULL` on the next stored proc call, isn't that more desirable? – pixelfreak Nov 27 '12 at 00:37