We provide IT Services

Register Now Click On

Read More Tutorials Click On

Post Page Advertisement [Top]

 

Table variable in SQL server

Definition

Table variables can be used in functions, stored procedures, and batches. To declare variables of type table. And Table variable is a type of local variable that allow to hold rows of data for temporary, so It is similar to temporary tables. And you can perform insert, update, delete and select all operations with Table variable.

 Note : Always remember that tempdb database is used to store table variables.

How do we declare a variable in SQL 

The name of table variable must be start with at(@) Symbol. so the syntax of the Table Variable is as follows.

Syntax :- 


DECLARE @TableVariable TABLE
  (
     col1 DATATYPE,
     col2 DATATYPE,
     col3 DATATYPE,
     coln DATATYPE
  )

DECLARE @TableVariable_Employee TABLE
  (
     id      INT,
     NAME    VARCHAR(20),
     age     INT,
     address VARCHAR(500),
     salary  DEC(12, 2) NOT NULL
  ) 

insert into @TableVariable_Employee values(1,'Anam Agrawal',25,'Noida',5000)  
insert into @TableVariable_Employee values(2,'Princi Agrawal',22,'Delhi',1500)  
insert into @TableVariable_Employee values(3,'Anshika Agrawal',36,'Gurgaon',6555)  
insert into @TableVariable_Employee values(4,'Radhika Agrawal',45,'Bangalore',25000)  

Select * from @TableVariable  


Remarks


Table variables are automatically cleaned up at the end of the function, stored procedure, or batch in which they are defined.

table variables that are used in stored procedures cause after stored procedure recompilations than when temporary tables are used when there are no cost-based choices that affect performance.

Examples :

A. Inserting data into the table variables

Once declared, the table variable is empty. You can insert rows into the table variables using the INSERT statement:

INSERT INTO @TableVariable_EmployeeSELECT NAME,
       age,
       address,
       salary
FROM   dbo.tbl_employee


1 comment:

Anonymous said...

Good article

Post a Comment

| Designed by Rockprogrammer