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?

Improve this question
2
  • 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? Commented Nov 27, 2012 at 0: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" Commented Nov 27, 2012 at 0:36

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.