When you required to use the current result set in next queries then store result into Table variables. We can Use table variable In UDF.
we will go through some questions on it.
How to Create?
How to use it?
Where it is Stored?
Can we create Index on it?
Can we create constraint on it?
Can we declare table variable in Stored Procedure?
Can we declare table variable in Function?
How to Drop it?
scope
Name overwritten
How to Create?, and How to use it?
Declare @Emp Table
(
Emp_Id INT Primary key,
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
Insert Into @Emp values(1,'ABC','Project1')
Insert Into @Emp values(2,'LMN','Project2')
Select * from @Emp
Where it is Stored?
Table variable is stored in Temp Database (one of system Database)
How to see it, execute below query to check,
Declare @Emp Table
(
Emp_Id INT Primary key,
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
Insert Into @Emp values(1,'ABC','Project1')
Insert Into @Emp values(2,'LMN','Project2')
go 1000
Select * from @Emp
Can we create Index on it?
Declare @Emp Table
(
Emp_Id INT Primary key, -----While creating table we can create index
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
After table variable declaration we can't create index on it.
Can we create constraint on it?
After table variable declaration we can't create constraint on it.
In short we can say after table variable declaration we can't alter / modify definition of it.
Can we declare table variable in Stored Procedure? --yes
Can we declare table variable in Function? --yes
How to Drop it?
No need to explicitly drop it.
scope
Scope of table variable limited to Batch.
Name overwritten
In One session we can't declare it 2 times,
Declare @Emp Table
(
Emp_Id INT Primary key,
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
Insert Into @Emp values(1,'ABC','Project1')
Insert Into @Emp values(2,'LMN','Project2')
Select * from @Emp
Declare @Emp Table
(
Emp_Id INT Primary key,
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
Insert Into @Emp values(1,'ABC','Project1')
Insert Into @Emp values(2,'LMN','Project2')
Select * from @Emp
Error
Msg 134, Level 15, State 1, Line 36
The variable name '@Emp' has already been declared. Variable names must be unique within a query batch or stored procedure.
Solution - Use go in between.
Declare @Emp Table
(
Emp_Id INT Primary key,
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
Insert Into @Emp values(1,'ABC','Project1')
Insert Into @Emp values(2,'LMN','Project2')
Select * from @Emp
GO
Declare @Emp Table
(
Emp_Id INT Primary key,
Emp_Name VARCHAR(30),
Project_Name VARCHAR(30)
)
Insert Into @Emp values(1,'ABC','Project1')
Insert Into @Emp values(2,'LMN','Project2')
Select * from @Emp
Comments
Post a Comment
If you have any doubt then please let me know in comment section.