Variables in sql server
In sql server there are three types of variables
Local variables in sql server:
Syntax for declaration and initialization of local variable in sql server:
DECLARE {@local_variable [AS] data_type [ = value ]} [,...n]
Where:
@local_variable: It is name of the variable.
Local variable or identifier naming rule in sql server:
1. Variable name must be started with @ character.
2. Variable names can have alphabets (including some character from other languages), digits, #, @ and $.
3. Variables name are case sensitive.
4. It cannot be system variable like @@ROWCOUN, @@ERROR etc.
Valid name of identifiers:
1. @a
2. @123
3. @#
4. @@@
5. @#@$1b_
6. @VARCHAR
Invalid name:
1. abc
2. @total value
3. @@IDENTITY
data_type: It can be any data type of sql server including CLR data type except text, ntext, or image.
Value: It can be any constant or expression which can explicitly or implicitly convertible into its data type.
Note: It is bad practice to start variables name by @@ since it is used in the naming of system variable name.
Examples:
Declaration of a local variable of type integer
DECLARE @empId INTEGER
Or
DECLARE @empId AS INTEGER
Declaration of more than one variable at the same time:
DECLARE
@empId AS INTEGER,
@empName AS VARCHAR(30),
@isActive BIT
Declaration and initialization of variable at the same time:
Default value of all local variables is NULL. We can initialize the local variable at the time of declaration. For example:
1. DECLARE @empId INTEGER = 100
2.
DECLARE
@empId AS INTEGER = 10,
@empName AS VARCHAR(30)= 'David',
@empAge INTEGER,
@isActive AS BIT = 0
Initialization of local variable in sql server:
We can also initiate the local variables after the declaration.
For more details go through the link: Initialization of variables in sql server
Scope of local variables:
Scope of local variables is batch. We can think batch as a set of sql queries which are executed at the same time. Stored procedure, functions, triggers are examples of named batch. If we declare a local variable in a particular batch, it will no longer available in the other batch. For example:
Suppose we execute a sql query:
DECLARE @CompanyName AS VARCHAR(50)
Now we assign a value to it and execute it
SET @CompanyName = 'Exact help'
Then we will get an error message: Must declare the table variable "@CompanyName”
Since scope of the @CompanyName variable was only up to first batch. So we have to assign a value to in the same batch that we have to execute both statements in same batch, for example:
DECLARE @CompanyName AS VARCHAR(50)
SET @CompanyName = 'Exact help'
Note: Go keyword in sql server always created a new batch. So if we will execute following set of sql queries:
DECLARE @CompanyName AS VARCHAR(50)
Go
SET @CompanyName = 'Exact help'
We will get same error that is: Must declare the table variable "@CompanyName"
Use of local variables in sql server.
1. We can use local variable to store values returned from function, sql query etc and perform any operation on it. For example:
DECLARE @Id AS INTEGER
SET @Id = SCOPE_IDENTITY()
DECLARE @Marks INT
DECLARE @Name AS VARCHAR(50)
SELECT
@Name = Name,
@Marks = Marks
FROM Student WHERE StuId = 2
2. Local variables can be used as parameters of stored procedure and function. For example:
CREATE PROCEDURE PrintMsg(
@Msg AS VARCHAR(100)
)
AS
BEGIN
SELECT @Msg AS [Message]
END
Execution of above stored procedure:
EXECUTE PrintMsg 'Exact Help'
3. It can also be used as rerun type of function.
Table variables in sql server:
Syntax for declaration of table variables
DECLARE @table_variable [AS]
{TABLE (
{ <column_definition>} [ ,... ]
)}
| <user_defined_table_type>
Where
@table_variable: Name of the table variable. Naming rule will same as the naming of local variable in sql server.
<column_definition>: Definitions of all columns same as normal table which we generally create using CREATE TABLE statement. All the column definitions are separated by comma operator.
Note: We can also include column as well as table label constraints like PRIMARY KEY, NOT NULL etc.
<user_defined_table_type>: We can also defined table using the user defined table type. For more detail go through the: How to create user defined table type.
Example:
Declaration of simple table variable in sql server:
DECLARE @Employee AS TABLE(
EmpId INTEGER,
EmpName VARCHAR(100),
DOJ DATETIME
)
Declaration of table variable including constraints in sql server:
DECLARE @Employee AS TABLE(
EmpId INTEGER IDENTITY PRIMARY KEY,
EmpName VARCHAR(100) NOT NULL,
DOJ DATETIME DEFAULT(GETDATE())
)
Declaration of table variable using user defined table type in sql server:
CREATE TYPE Employee AS TABLE(
EmpId INTEGER IDENTITY PRIMARY KEY,
EmpName VARCHAR(100) NOT NULL,
DOJ DATETIME DEFAULT(GETDATE())
)
DECLARE @Employee AS Employee
Sql query on table variable:
Insert statement:
INSERT INTO @Employee(EmpName,DOJ)
VALUES('Ritesh','10/22/2011')
Select statement:
SELECT * FROM @Employee
Update statement:
UPDATE @Employee SET
EmpName = 'manish'
WHERE EmpId = 1
Delete statement:
DELETE FROM @Employee
In general we can say we can perform the entire sql query on table variables which we can perform on normal tables.
Scope of table variables:
Scope of table variables is batch. We can think batch as a set of sql queries which are executed at the same time. Stored procedure, functions, triggers are examples of named batch. If we declare a table variable in a particular batch will no longer available in the other batch. For example:
Suppose we execute a sql query:
DECLARE @Student AS TABLE(
RollNo INT,
Name VARCHAR(30)
)
Now we execute a select query on this table variable
SELECT * FROM @Student
Then we will get an error message: Must declare the table variable "@Student"
Since scope of the @Student table was only up to first batch. So to perform any sql query on the @Student table we have to execute the declaration and perform operation at same batch that is
DECLARE @Student AS TABLE(
RollNo INT,
Name VARCHAR(30)
)
SELECT * FROM @Student
Note: Go keyword in sql server always created a new batch. So if we will execute following set of sql queries:
DECLARE @Student AS TABLE(
RollNo INT,
Name VARCHAR(30)
)
Go
SELECT * FROM @Student
We will get same error that is: Must declare the table variable "@Student"
Use of the table variables in sql server:
1. We can use table variables to store the values in a batch to perform some operation on it. For example suppose we a have student table like this:
StuId
|
Name
|
Subject
|
Marks
|
1
|
Robert
|
Phy
|
60
|
2
|
Pacino
|
Phy
|
54
|
3
|
Pacino
|
Che
|
45
|
4
|
Robert
|
Che
|
56
|
5
|
Pacino
|
Bio
|
67
|
6
|
Nicholson
|
Che
|
32
|
7
|
Nicholson
|
Phy
|
41
|
8
|
Nicholson
|
Bio
|
44
|
9
|
Robert
|
Bio
|
72
|
DECLARE @Student AS TABLE(
RollNo INT,
Name VARCHAR(30)
)
INSERT INTO @Student
SELECT TOP(5) StuId,Name FROM Student
2. To pass as parameters of stored procedures or functions but it must be read only. For example:
CREATE TYPE StudentType AS TABLE(
RollNo INT,
Name VARCHAR(30)
)
GO
CREATE PROCEDURE GetStudentInfo(
@StudentData StudentType READONLY,
@InsertedBy VARCHAR(50),
@InsertedData DATETIME = NULL
)
AS
BEGIN
SELECT @InsertedBy AS InsertedBy , *
FROM @StudentData
END
GO
DEClARE @Student AS StudentType
INSERT INTO @Student
SELECT TOP(5) StuId,Name FROM Student
EXECUTE GetStudentInfo @Student,'Davis'
3. Table variable can also use as return type of function. For example:
CREATE FUNCTION GetStudentInfo()
RETURNS @StudentData TABLE(
RollNo INT,
Name VARCHAR(30)
)
AS
BEGIN
INSERT INTO @StudentData
SELECT TOP(5) StuId + 100,Name FROM Student
RETURN
END
SELECT * FROM [dbo].[GetStudentInfo] ()
Output:
Roll No
|
Name
|
101
|
Robert
|
102
|
Pacino
|
103
|
Pacino
|
104
|
Robert
|
105
|
Pacino
|
Note: A function which returns table types is known as tabled value function otherwise it is called scalar functions.
Cursor variable in sql server
Syntax for declaration of cursor variables
DECLARE {@cursor_variable_name CURSOR}[,...n]
Where:
@cursor_variable: It is the name of the cursor variable
Example:
DECLARE @Student_Id_Cur CURSOR
Use of cursor variable:
We can use cursor variable as output parameters of stored procedures. Cursor type parameters of stored procedure must be varying type and output parameter. For example:
CREATE PROCEDURE GetStudentInfo(
@Student_Id_Cur CURSOR VARYING OUTPUT
)
AS
BEGIN
SET @Student_Id_Cur = CURSOR FOR
SELECT TOP(3) StuId FROM Student
OPEN @Student_Id_Cur
END
Execution of stored procedure:
DECLARE @Student_Id_Cur CURSOR
DECLARE @RollNo INTEGER
EXECUTE GetStudentInfo @Student_Id_Cur OUTPUT
FETCH @Student_Id_Cur INTO @RollNo
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT * FROM Student WHERE StuId = @RollNo
FETCH NEXT FROM @Student_Id_Cur INTO @RollNo
END
Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples
What is clustered index in sql server
What is non clustered index in sql server
No comments:
Post a Comment