In this article, I am giving a quick overview about TSQL in
SQL Server. After completing this article you will understand:
What is meant by TSQL?
What is a TSQL Block?
What are the types of TSQL Block?
What are the advantages of TSQL?
How to declare Variable, assign value to
variable & print variable value?
How to use IF – Else conditonal statement in
TSQL?
How to use WHILE loop in TSQL?
How to use CASE Function?
Please give your valuable suggestions and feedback to improve
this article.
What is meant by TSQL?
·
TSQL
Stand for Transact Structure Query Language. In Oracle it is known as PL/SQL.
·
TSQL is
a combination of SQL along with the procedural features of programming
languages.
·
TSQL program
consists of SQL & programming
language statements which form a
TSQL block.
·
We can control the program flow by using
conditional statements like IF and While loop.
·
Runtime Error Handling is also available using
the try catch mechanism.
·
It has syntax and rules that determine how
programming statements work together.
What is a TSQL Block?
A Simple TSQL Block contain three sections.
A Simple TSQL Block contain three sections.
- The Declaration section (optional)
- The Execution section (mandatory)
- The Exception (or Error) Handling section (optional)
1- Declaration Section:
The Declaration section of a TSQL Block starts with the
reserved keyword DECLARE. This
section is optional and is used to declare any placeholders like variables,
constants, records and cursors, which are used to manipulate data in the
execution section. Placeholders may be any of Variables, Constants and Records,
which stores data temporarily. Cursors are also declared in this section.
TSQL Placeholders
- Placeholders are temporary storage area.
- Placeholders can be any of Variables, Constants and Records.
- Placeholders used to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
- Depending on the kind of data you want to store, you can define placeholders with a name and a data type.
The Execution section of a TSQL Block starts with the
reserved keyword BEGIN and ends with
END. This is a mandatory section and
is the section where the program logic is written to perform any task. The
programmatic constructs like loops, conditional statement and SQL statements
form the part of execution section.
3- Exception Section:
3- Exception Section:
The Exception section of a TSQL Block starts with the
reserved keyword EXCEPTION. This
section is optional. Any errors in the program can be handled in this section,
so that the TSQL Blocks terminates gracefully. If the TSQL Block contains
exceptions that cannot be handled, the Block terminates abruptly with errors.
What are the types of TSQL Blocks?
Program Blocks can be of 2 types.
1- Anonymous Blocks.
2- Sub-Program Blocks.
Anonymous Blocks - They are unnamed block of code
for execution which can we written at a point where they are to be executed.
They can be written on a query window and execute.
Sub-Program Blocks - These are nothing but named
block of code for execution, where the program blocks are given a name for
identification. These will be stored on the database which provides reusability
of code.
What are the advantages of TSQL?
- Block Structures: TSQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. TSQL Blocks can be stored in the database and reused.
- Procedural Language Capability: TSQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
- Better Performance: TSQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
- Error Handling: TSQL handles errors or exceptions effectively during the execution of a TSQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.
How to Declare Variable, Assign Value to a Variable and Print Variable Value?
These are placeholders that store the values that can
change through the TSQL Block.
- TSQL allows the nesting of Blocks within Blocks i.e., the Execution section of an outer block can contain inner blocks.
- A variable which is accessible to an outer Block is also accessible to all nested inner Blocks.
- The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.
- Local variables - These are declared in inner block and cannot be referenced by outside Block.
- Global variables - These are declared in outer block and can be referenced by itself and by its inner blocks.
General Syntax to
Declare a variable
While declaring variables it has to be preceded with @ symbol.
DECLARE @Variable_Name [Data Type] [Width]
Example-
PRINT @Salary
General Syntax to Assigning
Values to Variables
Method 1 - Values can be assigned by using a SET Statement.
Example: SET @Salary=5000
Method 2 - We can also assign values to variables directly from the database
columns by using the below syntax
SELECT @Salary =
<column_name>
From <table_name> Where [Condition]
Method 3 - Value can be assigned from SQL query.
Example: SET @Salary = (SELECT <columnname>
FROM <table_name> WHERE <Condition>)
General Syntax for
Printing Variable Value
If we want to print the value of variable we can use
PRINT statement with the below syntax.
PRINT @Salary
How to use IF - ELSE conditional statement in TSQL?
IF else statement Imposes conditions on the executions of
a TSQL statement.
The TSQL statement that follows an IF keyword and its
condition are executed if the condition is satisfied: the Boolean expression
returns true. The optional ELSE keyword introduces another TSQL statement that
is executed when the IF condition is not satisfied; the Boolean expression
returns FALSE.
Syntax
IF Boolean Expression
[BEGIN]
<SQL Statement / Statement Block>
[END]
ELSE IF Boolean Expression
[BEGIN]
<SQL Statement / Statement Block>
[END]
ELSE Boolean Expression
[BEGIN]
<SQL Statement / Statement Block>
[END]
Example:
Declare @week int
Set @week=Datepart(DW,Getdate())
If @week=1
Print 'SUNDAY'
Else if @week=2
Print 'MONDAY'
Else IF @week=3
Print 'TUESDAY'
Else PRINT 'WEDNESDAY OR THRISDAY
OR FRIDAY OR SATURDAY'
How to use While Loop in TSQL?
In TSQL WHILE
Loop sets a condition for the repeated execution of an SQL statement or
statement block. The statements are executed repeatedly as long as the
specified condition is true. The execution of the statements in the WHILE loop
can be controlled from inside the loop with the BREAK and CONTINUE keywords.
Synatax:
WHILE Boolean Expression
[BEGIN]
<SQL Statement/ Statement block>
[BREAK]
<SQL Statement/ Statement block>
[CONTINUE]
<SQL Statement/ Statement block>
[END]
If there are multiple statements being enclosed then we
can put them under BEGIN and END statements.
Example: Below example printing 0-9
Declare @x int
Set @x=0
WHILE @x<10
BEGIN SET @x=@x+1
PRINT @X
ENDHow to use CASE Function in TSQL?
Case function evaluates a list of conditions and returns one of multiple possible result expressions.
It has two formats, Both the formats supports an optional ELSE argument.
1- The simple case function compares an expression to a set of simple expressions to determine the result.
syntax:
CASE <expression>
WHEN when_expression THEN result_expression
WHEN when_expression THEN result_expression
.....
ELSE else_result_expressoin
END
Example:
DECLARE @WEEK INT
SET @WEEK=DATEPART(DW,GETDATE())
SELECT CASE @WEEK
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUSEDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THRUSDAY'
WHEN 6 THEN 'FRIDAY'
ELSE 'SATURDAY'
END
2- The searched case function evaluates a set of Boolean expressions to determine the result.
syntax:
CASE
WHEN condition THEN result_expression
WHEN condition THEN result_expression
.....
ELSE else_result_expressoin
END
Example:
DECLARE @WEEK INT
SET @WEEK=DATEPART(DW,GETDATE())
SELECT CASE
WHEN @WEEK=1 THEN 'SUNDAY'
WHEN @WEEK=2 THEN 'MONDAY'
WHEN @WEEK=3 THEN 'TUSEDAY'
WHEN @WEEK=4 THEN 'WEDNESDAY'
WHEN @WEEK=5 THEN 'THRUSDAY'
WHEN @WEEK=6 THEN 'FRIDAY'
ELSE 'SATURDAY'
END
No comments:
Post a Comment