"@" symbol in stored procedure?

Question

I tried finding an answer to this online, but could not find any clear explanation:

Does the @ in a stored procedure serve some sort of special purpose/signify something in particular? I am a little confused as to when we use it, since examples seem to vary on its usage.

For instance in the following example @ is used:

DELIMITER $

DROP PROCEDURE IF EXISTS emp_count_2;

CREATE PROCEDURE emp_count_2(OUT param1 INT)
BEGIN
 SELECT COUNT(*) INTO param1 FROM Employee;
END
$
DELIMITER ;


/* To invoke this procedure use the mysql command statement
   CALL emp_count_2(@empCount);
   SELECT @empCount; 
*/

Once again, does the @ in this example serve some sort of special purpose, or can we remove the @ and just use normal variable names?

**EDIT: I am using MySql

to access reff: [**session variables in MySQL**](http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference). — Grijesh Chauhan, Mar 10 '13 at 14:52
@GrijeshChauhan is @ used only access var value outside the stored procedure? because we don't use @ inside of stored proc... so is @ just part of sql script syntax?? — rrazd, Mar 10 '13 at 14:55
@rrazd ok and if not stored variable [like here used](http://dba.stackexchange.com/questions/33497/query-all-employee-with-sum-of-salary-500?answertab=votes#tab-top). **?** — Grijesh Chauhan, Mar 10 '13 at 14:58

score 8 · Accepted Answer · answered Mar 10 '13 at 15:19

The @variable syntax in MySQL denotes a user-defined session variable. You can set these user variables outside a stored procedure, but you can also set them inside a stored procedure, and the effect is that the variable retains the value after your procedure call returns.

So in your example, the following would also do the same thing:

CREATE PROCEDURE emp_count_2()
BEGIN
 SELECT COUNT(*) INTO @empCount FROM Employee;
END

CALL emp_count_2(); /* sets @empCount as a side-effect */
SELECT @empCount;

It's okay for multiple sessions to set the user variable in this way concurrently, because user variables are scoped to a single session, and concurrent sessions may have variables of the same name, but with different values.

The variable syntax with no @ prefix is for variables local to the procedure, either procedure parameters, or else local variables declared with DECLARE within the procedure body.

This usage you have, passing a user variable as a parameter and assigning it in the body of the procedure, is useful if you want to call a procedure several times and store the result in separate user variables. Otherwise each call to the procedure would overwrite the previous value in the @empCount user variable for the current session.

"@" symbol in stored procedure?

1 Answers1

Linked