Google+ Followers

Tuesday, September 2, 2014

TSQL Programming Basics


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.
  1. The Declaration section (optional)
  2. The Execution section (mandatory)
  3. 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. 
2- Execution Section:

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:

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?
  1. 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.
  2. Procedural Language Capability: TSQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
  3. Better Performance: TSQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
  4. 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- DECLARE @Salary INT

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: Below example will print the day of week
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
END

How 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'
WHENTHEN '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=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

I hope you enjoyed the article don't forget to share on Facebook. 

No comments: