Oct 23, 2013

Variables in sql server 2008 R2

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 

No comments:

Post a Comment