Variable in SQl Server

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 = 'SQL Tips'

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 = 'SQL Tips'

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 = 'SQL TQL'

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 'SQL TQL'

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