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.
  • 1797

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

Clipboard02.jpg


© 2013 dotNetheaven. All rights reserved.