What is meant by T-SQL?
What is a T-SQL Block?
What are the types of T-SQL Block?
What are the advantages of T-SQL?
How to declare Variable, assign value to variable & print variable value?
How to use IF – Else conditional statement in T-SQL?
How to use WHILE loop in T-SQL?
How to use CASE Function?
What is meant by T-SQL?
What is a TSQL Block?
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 T-SQL 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.
T-SQL 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 T-SQL 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 T-SQL 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 T-SQL Blocks terminates gracefully. If the T-SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
What are the types of T-SQL 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 re-usability of code.
What are the advantages of T-SQL?
Block Structures: T-SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. T-SQL Blocks can be stored in the database and reused.
Procedural Language Capability: T-SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: T-SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
Error Handling: T-SQL handles errors or exceptions effectively during the execution of a T-SQL 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 T-SQL Block.
T-SQL 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 T-SQL?
IF else statement Imposes conditions on the executions of a T-SQL 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 T-SQL 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 T-SQL?
In T-SQL 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.
Syntax:
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 T-SQL?
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
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.
What is a T-SQL Block?
What are the types of T-SQL Block?
What are the advantages of T-SQL?
How to declare Variable, assign value to variable & print variable value?
How to use IF – Else conditional statement in T-SQL?
How to use WHILE loop in T-SQL?
How to use CASE Function?
What is meant by T-SQL?
- T-SQL Stand for Transact Structure Query Language. In Oracle it is known as PL/SQL.
- T-SQL is a combination of SQL along with the procedural features of programming languages.
- T-SQL 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.
- Run time 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.
The Declaration section (optional)
The Execution section (mandatory)
The Exception (or Error) Handling section (optional)
1- Declaration Section:
The Declaration section of a T-SQL 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.
T-SQL 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 T-SQL 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 T-SQL 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 T-SQL Blocks terminates gracefully. If the T-SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
What are the types of T-SQL 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 re-usability of code.
What are the advantages of T-SQL?
Block Structures: T-SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. T-SQL Blocks can be stored in the database and reused.
Procedural Language Capability: T-SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: T-SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
Error Handling: T-SQL handles errors or exceptions effectively during the execution of a T-SQL 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 T-SQL Block.
T-SQL 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 T-SQL?
IF else statement Imposes conditions on the executions of a T-SQL 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 T-SQL 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 T-SQL?
In T-SQL 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.
Syntax:
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 T-SQL?
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
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 Variable
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 SQLserver.
@@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.
WHEN @WEEK=3 THEN 'TUSEDAY'
WHEN @WEEK=4 THEN 'WEDNESDAY'
WHEN @WEEK=5 THEN 'THRUSDAY'
WHEN @WEEK=6 THEN 'FRIDAY'
ELSE 'SATURDAY'
END
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 Variable
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 SQLserver.
@@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.
