When we create a stored procedure propably we create some variable like this :
DECLARE @UserCount INT
We use that variable in stored procedure. But we don't dispose that variable at the end of stored procedure. We dispose Cursors with DEALLOCATE but we don't write ant lines for variables which we create.
Another stuation is we create tables and we name them "@table"
When and how Sql Server disposes that kind of variables?
Per MSDN:
"The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared."
http://msdn.microsoft.com/en-us/library/ms187953.aspx
On a side note - you may see some very interesting performance differences between temp tables (#MyTable) and table variables (@MyTable). If we're profiling a sproc and seeing performance problems in the use of our table variable, we'll try it with a temp table instead to see if this can help improve performance.
Adding to Bob's answer above, specifically regarding the point
We dispose Cursors with DEALLOCATE but we don't write ant lines for variables
MSDN states and i quote :
A cursor variable does not have to be explicitly deallocated. The variable is implicitly deallocated when it goes out of scope
This is applicable atleast as far back as SQL Server 2000