SQL SERVER – TSQL PROGRAMMING

• TSQL (Transact SQL) Programming is an Procedural Language Extension to SQL which is known as PL/SQL in Oracle.
• It extends SQL by adding programming structures and subroutines available in any high level language.
• It has syntax and rules that determine how programming statements work together.
• We can control the program flow by using conditional statements like IF and While loop
• Runtime Error Handling is provided using the try catch mechanism
• Reusability of the code is available by defining objects such as Procedures and Functions.
• SQL Commands can be embedded inside the programs.
• 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 be 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 the reusability of code.

Program Blocks like in any other language provides option for variable declaration, program logic using conditional statements and displaying the results to the user, in the same way we can define programs in SQL Server also.

Declaring Variables:

While declaring variables it has to be preceded with @ symbol.

Syntax: DECLARE @<var> [AS] <data_type> [,…n]

DECLARE @X INT

DECLARE @SAL AS MONEY

DECLARE @ENAME VARCHAR(50), @JOB VARCHAR(50)

Assinging Values to Variables: Values can be assigned by using a SET statement.

Syntax: SET @<var> = <value>

SET @X=100

SET @ENAME='SCOTT'

Printing Values:

If we want to print the values we can use the Print statement.

Syntax: Print @<var>

Print @X

Print @Ename

Conditional Statements:

If … Else If … Else: Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL 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 Transact-SQL statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.

IF Boolean_expression

[ BEGIN ]

< sql_statement | statement_block >

[ END ]

[ ELSE IF Boolean_expression

[ BEGIN ]

< sql_statement | statement_block >

[ END ]

ELSE

[ BEGIN ]

< sql_statement | statement_block > ]

[ END ]

-If there are multiple statements being enclosed between each block then we can put them under Begin and End Statements.

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 IF @WEEK=4

PRINT 'WEDNESDAY'

ELSE IF @WEEK=5

PRINT 'THURSDAY'

ELSE IF @WEEK=6

PRINT 'FRIDAY'

ELSE IF @WEEK=7

PRINT 'SATURDAY'

CASE FUNCTION:

The case function what we have discussed under the System Functions can also be used here as following:

DECLARE @WEEK INT

SET @WEEK=DATEPART(DW, GETDATE())

SELECT CASE @WEEK

WHEN 1 THEN 'SUNDAY'

WHEN 2 THEN 'MONDAY'

WHEN 3 THEN 'TUESDAY'

WHEN 4 THEN 'WEDNESDAY'

WHEN 5 THEN 'THURSDAY'

WHEN 6 THEN 'FRIDAY'

WHEN 7 THEN 'SATURDAY'

END

-This can be written in the second style of the CASE Statement also that has been discussed in the SQL as following:

DECLARE @WEEK INT

SET @WEEK=DATEPART(DW, GETDATE())

SELECT CASE

WHEN @WEEK=1 THEN 'SUNDAY'

WHEN @WEEK=2 THEN 'MONDAY'

WHEN @WEEK=3 THEN 'TUESDAY'

WHEN @WEEK=4 THEN 'WEDNESDAY'

WHEN @WEEK=5 THEN 'THURSDAY'

WHEN @WEEK=6 THEN 'FRIDAY'

WHEN @WEEK=7 THEN 'SATURDAY'

END

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 statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

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.

BREAK:

Causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.

CONTINUE:

Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

Program 1:

DECLARE @X INT

SET @X=0

WHILE @X<10

BEGIN

SET @X=@X+1

PRINT @X

END

Program 2:

DECLARE @X INT

SET @X=0

WHILE @X<10

BEGIN

SET @X=@X+1

IF @X=6 BREAK

PRINT @X

END

-In this case the break statement brings the control out of the loop printing from 1 to 5.

Program 3:

DECLARE @X INT

SET @X=0

WHILE @X<10

BEGIN

SET @X=@X+1

IF @X=6 CONTINUE

PRINT @X

END

-In this case the continue statement will skip the print statement when the value of x is 6 so prints from 1 to 5 and 7 to 10.

Comments in TSQL:

Comments will be ignored will executing the program, they will increase the readability and aids understanding of the program.

• Single Line Comments (–)
• Multi Line Comments (/* ….. */)

Assinging values from columns into variables: Till now we were assigning static values to the variables using the SET statement, but we can also assign values from a column into the variables as following:

SELECT @<var>=<col_name> [, ……n] FROM <table_name> [CONDITIONS]

SELECT @ENAME=ENAME FROM EMP WHERE EMPNO=1001

-A simple TSQL program which takes the Empno and prints the Name and Salary.

DECLARE @EMPNO INT, @ENAME VARCHAR(50), @SAL MONEY

SET @EMPNO=1005

SELECT @ENAME=ENAME, @SAL=SAL FROM EMP WHERE EMPNO=@EMPNO

PRINT @ENAME + ' EARNS ' + CAST(@SAL AS VARCHAR)

-A Program which takes the Empno and increments the Salary of the person on the following criteria:

If Job is President increment with 10%

If Job is Manager increment with 8%

If Job is Analyst increment with 6%

If Job is any thing other incrment with 5%

DECLARE @EMPNO INT, @JOB VARCHAR(50)

SET @EMPNO=1005

SELECT @JOB=JOB FROM EMP WHERE EMPNO=@EMPNO

IF @JOB='PRESIDENT'

UPDATE EMP SET SAL = SAL + SAL * 0.1 WHERE EMPNO=@EMPNO

ELSE IF @JOB='MANAGER'

UPDATE EMP SET SAL = SAL + SAL * 0.08 WHERE EMPNO=@EMPNO

ELSE IF @JOB='ANALYST'

UPDATE EMP SET SAL = SAL + SAL * 0.06 WHERE EMPNO=@EMPNO

ELSE

UPDATE EMP SET SAL = SAL + SAL * 0.05 WHERE EMPNO=@EMPNO

-In the above case which empno has been provided for the variable @EMPNO first it will check for the JOB of the employee and then it will increment the salary on the specified criteria .

-The problem in the above case is we can increment only one Employee Salary at a time but if we want to increase the Salary of Multiple Emloyees at the same time it is not possible, as multiple rows cannot be effected  within the program to over come this we use Cursors.

- Operations in a relational database act on a complete set of rows. The set of rows returned by a SELECT statement consists of all the rows that satisfy the conditions in the WHERE clause of the statement. This complete set of rows returned by the statement is known as the result set. Applications, especially interactive online applications, cannot always work effectively with the entire result set as a unit. These applications need a mechanism to work with one by one row at a time. Cursors are an extension to result sets that provide that mechanism.

Cursor extend result processing by:

• Allowing positioning at specific rows of the result set.
• Retrieving one row or block of rows from the current position in the result set.
• Supporting data modifications to the rows at the current position in the result set.
• Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.
• Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.