MS SQL Server 4 - Local and Global Variable

 Hello All,

In this article we will look into the usage of local and global variables.

LOCAL VARIABLES:

Local Variables are the variables created with the help of DECLARE Statement and Initial value assign to the variable with the help of SET or SELECT statement.

We can use local variable inside Store Procedures, Views, Triggers etc...

Syntax Goes Like below:

DECLARE @VARIABLE_NAME DATA_TYPE

Here is the simple example, we can get the idea about local variable.

DECLARE @GetCurrentDate DATETIME
SET @GetCurrentDate = GETDATE()
SELECT @GetCurrentDate AS CURRENTDATE

Below is the output for above query

GLOBAL VARIABLE:

We have another type of variable in SQL Server is called Global Variable.

Global Variables are the variable which is managed by system.

Global Variables cannot be set explicitly or declared by users.

It is mainly used to report the system activity like no of rows returned from select query, previous error triggered by SQL statement etc...

Here are the some common Global Variables available in SQL Server.

@@ROWCOUNT :

It returns the number of rows processed by preceding command.

Create table Employee

CREATE TABLE Course
(
	CourseId	Int Primary Key,
	Title		Varchar(300),
	DateStarts      DateTime,
	DateEnds	DateTime,
	Status		Int
)

Insert Some records to the table

INSERT INTO Course VALUES(1,'ASP.NET MVC',GETDATE()+5,GETDATE()+100,1)
INSERT INTO Course VALUES(2,'ASP.NET WEB API',GETDATE()+5,GETDATE()+100,1)
INSERT INTO Course VALUES(3,'ASP.NET CORE API',GETDATE()+5,GETDATE()+100,1)
INSERT INTO Course VALUES(4,'PYTHON',GETDATE()-100,GETDATE()-1,0)

Now select the records from Course table and get the number of records fetched from above statement

SELECT * FROM Course
SELECT @@ROWCOUNT AS TOTA_COURSE_FETCHED

And output is

@@SERVERNAME:

Returns the current SQL Server Name.

@@VERSION:

Returns the Current version of the SQL Server installed in the machine 

Example: 

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Home Single Language 10.0 <X64> (Build 22000: ) 

That's it for this article, In case any issues or concern please raise your concern through comment section.

Take care see you in next article.