Different types of variables in SQL Server

Introduction:

In this article i will explain Different types of Variables in SQL Server.

Description:

In previous articles i explained about joins, Batches, Stored procedure, Functions, Difference between Stored procedure and Function, and Triggers in SQL Server. Now i will explain Different types of Variables in SQL Server.

Variables:

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a valu is provided as part of the declaration.

SQL Server has two types of variables.

  1. Local variables - Local Variable names prefixed with '@' sumbol.
  2. Global Variables - Global Variable names prefixed with '@@' symbols.

Local Variables:

Declare and Assign values:

Syntax: 

Declare <@local_variable_name> [AS] data_type,...

Example: 

Declare @a int

Set @a = 10;

--(or)

Select @a = 10;

Select @a;

SQL SERVER 2008 – 2012 – Declare and Assign Variable in Single Statement:

Declare @a int = 10, @b int = 10, @Date datetime = GetDate()

Select @a as a, @b as b, @Date as [Date];

Output:

Global Variables:

SQL Server provides a massive number of global variables, which are very effective to use in our regular Transact-SQL. Global variables represent a special type of variable. The server always maintain the values of these variables. All the global variables represent information specific to the server or a current user session. 

Global variable names begin with a @@ prefix. You do not need to declare them, since the server constantly maintains them.

Note:

They are system-defined functions and you cannot declare them.

Global variables available in SQL Server 2008 are as follows:

1. @@CONNECTIONS:

The number of logins or attempted logins since SQL Server was last started.

Return Type: int

Example:

SELECT GETDATE() AS 'Today''s Date and Time', 
@@CONNECTIONS AS 'Login Attempts'

Output:

2. @@CPU_BUSY

The amount of time, in ticks, that the CPU has spent doing SQL Server work since the last time SQL Server was started.

Return type: int

Example:

SELECT    @@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) AS 'CPU microseconds', 
        GETDATE() AS 'As of';

Output:

3. @@CURSOR_ROWS
4. @@DATEFIRST
5. @@DBTS
6. @@DEF_SORTORDER_ID
7. @@DEFAULT_LANGID
8. @@ERROR

Commonly used to check the error status (succeeded or failed) of the most recently executed statement. It contains 0 if the previous transaction succeeded; otherwise, it contains the last error number generated by the system.

Return type: int

Example(1):

Declare @i int;
Set @i = 10/0;
print @@Error
IF @@ERROR <> 0 
    PRINT  'Your error message';
Else
    PRINT 'Your success message'

Output:

Note:

Every Transact-SQL statement resets @@error, including print statements or if tests, so the status check must immediately follow the statement whose success is in question.

When ever error occured then the @@error value updated with error_number. In the above example, it prints @@error value and then it printed else part message only. Why because, print is also a statement, it is succeeded. So, system resets @@error value to  zero.

Get Error Details:

To get details of a particular error see the following example.

Example(2):

Declare @a int, @Error_Number int;
Begin try
Set @a = 10/0;
Set @Error_Number = @@ERROR
End try
Begin Catch
IF @@ERROR != 0

    Select Error_Number() as [Error Number], Error_Message() as [Error Description]
    
End catch

Output:

Note:

Here Error_Number() and Error_Message() are predefined system functions.

9. @@FETCH_STATUS
10. @@IDENTITY

The last value inserted into an IDENTITY column by an insert or select into statement. @@identity is reset each time a row is inserted into a table. If a statement inserts multiple rows, @@identity reflects the IDENTITY value for the last row inserted. If the affected table does not contain an IDENTITY column, @@identity is set to 0.

The value of @@identity is not affected by the failure of an insert or select into statement, or the rollback of the transaction that contained it. @@identity retains the last value inserted into an IDENTITY column, even if the statement that inserted it fails to commit.

Return type: numeric(38,0)

11. @@IDLE

The amount of time, in ticks, that SQL Server has been idle since it was last started.

Return type: int

12. @@IO_BUSY

The amount of time, in ticks, that SQL Server has spent doing input and output operations since it was last started.

Return type: int

13. @@LANGID

The local language id of the language currently in use (specified in syslanguages.langid).

Return type: smallint

14. @@LANGUAGE

The name of the language currently in use (specified in syslanguages.name).

Return type: nvarchar

15. @@LOCK_TIMEOUT
16. @@MAX_CONNECTIONS

The maximum number of simultaneous connections that can be made with SQL Server in this computer environment. The user can configure SQL Server for any number of connections less than or equal to the value of @@max_connections with sp_configure ''number of user connections''.  

Return type: int

Example:

SELECT @@MAX_CONNECTIONS AS 'Max Connections'

Output:

17. @@MAX_PRECISION
18. @@MICROSOFTVERSION
19. @@NESTLEVEL
20. @@OPTIONS
21. @@PACK_RECEIVED

The number of input packets read by SQL Server since it was last started.

Return type: int

22. @@PACK_SENT

The number of output packets written by SQL Server since it was last started.

Return type: int

23. @@PACKET_ERRORS

The number of errors that have occurred while SQL Server was sending and receiving packets.

Return type: int

24. @@PROCID
25. @@REMSERVER
26. @@ROWCOUNT

The number of rows affected by the last command. @@rowcount is set to 0 by any command which does not return rows, such as an if statement. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request.

Return type: int

27. @@SERVERNAME

The name of the local SQL Server. You must define a server name with sp_addserver, and then restart SQL Server.

Return type: varchar

28. @@SERVICENAME
29. @@SPID

The server process ID number of the current process.

Return type: smallint

30. @@TEXTSIZE

The current value of the set textsize option, which specifies the maximum length, in bytes, of text or image data to be returned with a select statement. Defaults to 32K.

Return type: smallint

31. @@TIMETICKS

The number of microseconds per tick. The amount of time per tick is machine dependent.

Return type: int

32. @@TOTAL_ERRORS

The number of errors that have occurred while SQL Server was reading or writing.

Return type: int

33. @@TOTAL_READ
34. @@TOTAL_WRITE
35. @@TRANCOUNT

The nesting level of transactions. Each begin transaction in a batch increments the transaction count. When you query @@trancount in chained transaction mode, its value is never zero since the query automatically initiates a transaction.

Return type: int

36. @@VERSION

The date of the current version of SQL Server.

Return type: nvarchar

  • Created
    Dec 22, 2013
  • Updated
    Oct 03, 2020
  • Views
    2,758
Related Articles
What is Cross Join in SQL Server
How delete record from parent table when there is Foreign Key relation in SQL Server
What is the differences between CHAR, NCHAR, VARCHAR and NVARCHAR in SQL Server?
How to use Delete Command in SQL Server?
How to add or remove a column from a table in SQL Server?
How to insert values into Identity Column in SQL Server?
How to get a fixed-length value in SQL Server?
What is Left Join (or) Left Outer Join in SQL Server?
How to get current week dates in SQL Server?
What are the different types of Date formats in SQL Server?