SQL Server Transact-SQL

T-SQL is a Microsoft proprietary database language used for SQL Server. It is a procedural language unlike 4th generation language SQL and is used to extend the capability of the SQL. The procedural language like C provides different features of control of flow of program like loop, conditional statements etc. T-SQL provides all such features apart from the features of SQL.

Features of Transact-SQL

• T-SQL is proprietary SQL programming language developed by Microsoft.
• T-SQL provides procedural language features like C.
• T-SQL provides 4^th generation language features like SQL.
• T-SQL is not case sensitive language.
• T-SQL is organized into blocks of codes.
• A block begins with BEGIN keyword and ends with END keyword.
• The statements are written inside blocks and each statement terminates with semi-colon.
• The variables can be declared outside or inside block.
• The variables declared inside block is local to the block.
• The variables declared outside block is global to the block.
• Nested block is allowed in T-SQL.
• Variables declared in outer block can be accessed by inner block but not vice versa.

Procedural Language Features in T-SQL

There are some of the following procedural features of T-SQL which enables programmers to control the flow of program execution. They are as below:

• Break
• Continue
• While
• If else
• Return
• Throw
• Try catch
• Begin end

Variables in T-SQL

The concept of variable in T-SQL is about an object which can hold a single data value of a specific data type. To declare a variable, we use declare statement and to initialize value to the variable we use set statement. We can declare more than one variable in a single statement using comma separator.

The scope of variable is within the batch. We use the USE statement to activate a database. We can use SELECT @varlocal or SET @varlocal to assign a value to variable but using SET statement is recommended. Some examples are as follows:

Initialize Variables with SELECT Statement

DECLARE @var1 varchar (30);
SELECT @var1 = 'Ajeet Kumar';
SELECT @var1 AS 'Developer';

Initialize Variables with SET Statement

Begin
       DECLARE @number1 int;
       DECLARE @number2 int;
       DECLARE @result int;
       SET @number1 = 200;
       SET @number2 = 300;
       SET @result = @number1 + @number2;
       PRINT cast (@result as varchar (20));

End;

If Else Statements

IF DB_ID () = 1
PRINT 'The current database is master.';
ELSEIF DB_ID()= 2
       PRINT 'The current database is tempdb.';
ELSEIF DB_ID()= 3
       PRINT 'The current database is model.';
ELSEIF DB_ID()= 4
       PRINT 'The current database is msdb.';
Else
PRINT  'The current database is AppliedkDb.';
GO
PRINT DB_ID()

Go Statement

The GO statement is used to separate one batch from another. The important point is to remember that GO statement is actually not part of T-SQL. Therefore, we don't use semicolon after GO statement. If semicolon is used after it, error will be generated.

Begin End Block

The concept of BEGIN END block in SQL Server is very much similar to usage of curly braces {and} in programming languages like Java or C#. The begin end block is used to declare variables and T-SQL statements. We can use nested BEGIN END block very much like nested curly braces in java or C#.

BEGIN

    DECLARE variables

    T-SQL statements

END

All the statements written in a begin end block are executed successfully. In case of failure entire block is discarded. Without begin end block the following t-SQL script will be infinite loop.

WHILE for Iteration

DECLARE @Iteration Integer = 0
WHILE @Iteration <10
BEGIN
    SELECT FirstName, MiddleName
    FROM dbo.DimCustomer WHERE LastName = 'Adams';
SET @Iteration += 1
END;

In the above example, begin end block is executed again and again. In each iteration, the value of iteration variable is increased, and the current value is passed to the conditional expression of WHILE statement. Without begin end block, while block will have SELECT statement to execute for infinite times. The SET statement never will be executed.

Practical Examples

Example1. Create a table with columns named as Id, Quarter, Year, Sales having int data types. Insert some data into the table. Use case expression to display different quarters 1, 2, 3, and 4 as q1, q2, q3 and q4 respectively in select query.

Use AppliedkDB
go

create table tbl_sales
(
id int primary key identity
, quarter int not null
, year int not null
, sales int not null
)
go
insert into tbl_sales values
(1, 2020, 12000)
,(2, 2020, 13000)
,(3, 2020, 11000)
,(4, 2020, 10000)
go
select id, case
when quarter = 1 then 'q1'
when quarter = 2 then 'q2'
when quarter = 3 then 'q3'
when quarter = 4 then 'q4'
end as qtr
,year, sales
from tbl_sales;

Example2

Declare @number as int
set @number = 1

While @number < 10
begin
print @number
set @number = @number + 1
end

If you use select then tables will be displayed in the output.

Declare @number as int
set @number = 1

While @number < 10
begin
select @number
set @number = @number + 1
end

If you use select then tables will be displayed in the output. To avoid this, create a temporary table and insert data into it. Finally display the data.

Create table #temp( num int)
go
declare @number as int
set @number = 1

While @number < 10
begin
insert into #temp values(@number)
set @number = @number + 1
end

Select * from #temp

In the next post we will learn about Error Handling in SQL Server.