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:

(@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)

Additional Resources



Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.

Darren D.

View all FMS products for Microsoft Access All Our Microsoft Access Products



Free Product Catalog from FMS