How to Work with Table Variables in Transact-SQL
In this article I am going to explain about how to work with table variable in Transact-SQL.
Introduction
Transact-SQL (T-SQL) is Structured Query Language and it is Microsoft's and Sybase's proprietary. Transact-SQL is originally developed by IBM for querying, altering and defining relational database. Transact SQL is also known as T-SQL. T-SQL supports many feature like variables, loops, conditions and exceptions.
Table Variable
Table variable is used to store an entire result set rather than a single value. DECLARE statement is used to create a table variable. All the name of table variable in T - SQL start with at sign (@).
Syntax for declaring a variable in T-SQL
DECLARE @table_name data_type
Example
USE vipendra
DECLARE @vendorinfo TABLE
(vendorid INT, vendorname VARCHAR(40))
INSERT @vendorinfo
SELECT vendorid, vendorname
FROM dbo.mcnvendors
WHERE vendorid IN
(
SELECT vendorid
FROM dbo.mcninvoices
WHERE invoicetotal>500
)
SELECT * FROM @vendorinfo
|
Output