SQL Server : T-SQL Programming

T-SQL Programming :

T-SQL stands for TRANSACT-SQL. It is a programming language, which can be used to develop logic on the database.
Whenever we are trying to execute a set of statements in the query window, those statements will be execute independently. There will be no relation between the statements. In order to have some kind of relation and application logic to those statements, they can be specified under a T-SQL program.
T-SQL is a procedural language which contains a set of SQL statements as a unit. Like any other programming language, T-SQL also follows a predefined structure to develop the programs. This structure contains a 'DECLARE' block used to declare variables and 'BEGIN...END' block to specify me statements or logic to be executed.

Syntax :

 DECLARE 
   <Variable Declarations>
 BEGIN
    ---------------
      <Executable Statements>
    ---------------
 END 

T-SQL supports 2 types of variables. They are :

• Local Variable
• Global Varibale

Local Variables :

A local variable can be created by using the DECLARE statement. An initial value can be assigned to the variable with the help of the SELECT statement and can be used within the trigger or procedure where it is created or assigned the value.

Global Variables :

Global Variables are pre-defined and maintained by the system. The server to track server-wide and session-specific information uses them. They cannot be explicitly set or declared. Global variables cannot be defined by users and are not used to pass information across processors by applications. Many of the global variables report on system activity since the last time SQL server was started, other report information about a connection.

Some Common Global Variables are:

Global Variable	Description
@@rowcount	Returns the number of rows processed by preceding command.
@@error	Returns the error number of the last error generated.
@@trancount	Returns the transaction nesting level status.
@@servername	Returns the name of the local SQL server.
@@version	Returns the version of the SQL server using.
@@spid	Returns the current process ID.
@@identity	Returns the last identity value used in an insert.
@@nestlevel	Returns the number of level nested in a stored procedure/trigger.
@@fetch_status	Returns a value corresponding to the status of the previous fetch statement in a cursor.

Declaring Variables :

The declare statement can be used to provide variable declaration by specifying the name of the variable and its corresponding data type. Even thought we are declaring two variables of same type, each variable must be declared independently.

Syntax :

  DECLARE <variable> <datatype>

Example :
=======
     1. Declare @i int
     2. Declare @i, @j int (Error)     
     3. Declare @i int, @j int

Initializing Variables :

We can make use of either SELECT or SET statements to initialize variables.

Syntax :

 SELECT @varname = value (or) SET @varname = value

Example :
=======
     SELECT @name_variable='AKINOVA KURENDIOL', @age=22

Print Statement :

The PRINT statement is used to pass a message to the client program's message handler. IT is used to display user defined messages.

Syntax :

 PRINT character_string | @local_variable | @@global_variable

The message to be dispalyed using PRINT statement can be up to 255 characters long.