Declaring and Using Transact-SQL Variables in a Stored Procedure

Provided by Jim Ferguson, Project Manager

Most people are familiar with declaring and using Transact-SQL variables in stored procedures. Typically, these variables are used for temporary storage of scalar values such as integers, strings, dates and so forth. However, a variable may also be a table-type variable. You can use a table-type variable where you might otherwise use a temporary table. One reason you might want to use a table variable is to hold onto a small subset of data for use multiple times in the body of a stored procedure.

In this example, a table variable is created to hold a subset of data from the Northwind Orders table for a particular employee:

CREATE PROCEDURE stpTableVariable
(@EmpID int) as

-- create the table variable
declare @EmpOrders table (orderid int not null)

-- populate the table. In this case we take the results of a query on
-- another table, but you could do simple INSERT statements that take
-- literals as well:
insert into @EmpOrders (orderid)
select orderid
from orders where employeeid = @EmpID

-- Now use the results stored in the table variable as part of a where
-- clause. You could also do a join or any other standard SQL action
-- with the table variable
select * from orders
where orderid in (select orderid from @EmpOrders)