Table Variable Uses
Yesterday we considered some of the differences between Declared table variables and traditional temp tables created by defining a table prefixed with # or ##. Today I would like to present some cases to use Table Variables.
Table variables may be used in any SQL Statement, and only exist for the execution of SQL Statements either dynamically or by including them in an SQL Object. Once the SQL Transaction completes, the scope of the table variable ends, and any data contained no longer exists.
Table variables may be used in many ways…here are a few I find helpful:
- Input parameters to stored procedures
- Output results of a table value function that cannot be defined through a single inline SQL statement
- Temporary Lists around which you might define a cursor instead of locking live data in a Query
- Temporary work tables for optimization purposes
Perhaps we’ll drill into these different methods in future editorials. In the meantime, why not share different ways in which you utilize temp table variables or even temp tables if that is your preferred practice. You can leave a note below to get into the discussion, or drop me an Email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Putting Data to Work for Mid-Market Companies
read more)
Featured Script
sp_scripttable
I’ve recently encountered a situation, that I needed to get a table’s script in a stored procedure. I’ve created this stored… (read more)