#temp table or a @table variable

In a stored procedure, you often have a need for storing a set of data within the procedure, without necessarily needing that data to persist beyond the scope of the procedure.

1. Local Temporary Tables(# table)

CREATE TABLE #employee
(
empid INT,
empname VARCHAR(100)
)

• A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.
• The data in this #temp table is visible only to the current scope usually a stored procedure, or a set of nested stored procedures.
• The table gets cleared up automatically when the current procedure goes out of scope but try to delete before exiting stored procedure.
• Local temp tables name is limited to 116 characters.

Use DROP TABLE statment like following.

DROP TABLE #people

2.Table Variables(@ table)

DECLARE @employee TABLE
(
empid INT,
empname VARCHAR(100)
)

• A table variable is created in memory, and so performs slightly better than #temp tables.
• Table variables are automatically cleared when the procedure or function goes out of scope, so you don’t have to remember to drop or clear the data.
• You cannot truncate a table variable.
• Table variables cannot be altered after they have been declared.
• Unlike a #temp table, you cannot drop a table variable when it is no longer necessary—you just need to let it go out of scope.