Chapter 8: Database

programming on SQL Server

DATABASE PROGRAMMING ON SQL SERVER 1

 Objectives

 Understand what triggers are for and how to use

 Understand what stored-procedure are for and
how to use
 Understand what cursors are for and how to use
 Understand what functions are for and how to
use
 Understand the difference between T-SQL
programming with other programming languages
 Understand the useful of trigger, function, stored-
procedure (compared with SQL statements)
DATABASE PROGRAMMING ON SQL SERVER 2
 Contents

 T-SQL Programming
 Stored-procedure
 Functions
 Triggers
Cursors

DATABASE PROGRAMMING ON SQL SERVER 3

 Physical Diagram - FUHCompany

tblLocation
locNum
tblDependent
tblWorksOn depName
locName
empSSN empSSN
proNum depSex
workHours depBirthdate
depRelationship
tblDepLocation
depNum
locNum

tblProject tblEmployee
proNum empSSN
proName empName
tblDepartment
locNum depNum empAddress
depNum depName empSalary

mgrSSN empSex

mgrAssDate empBirthdate
depNum
supervisorSSN

THE DATABASE LANGUAGE SQL 4

Short introduction to T-SQL programming

1. Variables
◦ Declare a variable
DECLARE @local_variable [AS] data_type [=initialvalue] , …

data_type: any system-supplied, common language runtime (CLR) user-defined

table type. A variable cannot be of text, ntext, or image data type

◦ Example
DECLARE @empName NVARCHAR(20), @empSSN AS DECIMAL,
@empSalary DECIMAL=1000

DATABASE PROGRAMMING ON SQL SERVER 5

 Short introduction to T-SQL programming

1. Variables (cont)
◦ Assign a value into a variable : using SET or SELECT
SET @empName=N’Mai Duy An’
SELECT @empSalary=2000

◦ Assign a value into a variable using SQL command : SELECT or

UPDATE
SELECT @empName=empName, @empSalary=empSalary
FROM tblEmployee
WHERE empName=N'Mai Duy An‘

UPDATE tblEmployee
SET @empName=empName, @empSalary=empSalary
WHERE empName=N'Mai Duy An’

DATABASE PROGRAMMING ON SQL SERVER 6

 Short introduction to T-SQL programming

1. Variables (cont)
◦ Display value of a variable : using PRINT or SELECT
PRINT @empName
SELECT @empSalary

◦ Converts an expression from one data type to a different data

type : using CAST or CONVERT function
DECLARE @empName NVARCHAR(20), @empSalary DECIMAL
SET @empName=N'Mai Duy An'
SET @empSalary=1000
PRINT @empName + '''s salary is ' + CAST(@empSalary AS VARCHAR)
PRINT @empName + '''s salary is ' + CONVERT(VARCHAR, @empSalary)

DATABASE PROGRAMMING ON SQL SERVER 7

Short introduction to T-SQL programming

2. Flow-control statement
 Statement Blocks: Begin…End
 Conditional Execution:
IF ... ELSE Statement
CASE ... WHEN
 Looping: WHILE Statement
 Error handling:
@@ERROR
TRY … CATCH

DATABASE PROGRAMMING ON SQL SERVER 8

 Short introduction to T-SQL programming

Statement Blocks: BEGIN…END

◦ Groups of statements used with IF, WHILE, and CASE statements must be grouped
together using the BEGIN and END statements. Any BEGIN must have a
corresponding END in the same batch.

IF ... ELSE Statement

◦ evaluate a Boolean expression and branch execution based on the result

DECLARE @workHours DECIMAL, @bonus DECIMAL

SELECT @workHours=SUM(workHours)
FROM tblWorksOn
WHERE empSSN=30121050027
GROUP BY empSSN

IF (@workHours > 300)

SET @bonus=1000
ELSE
SET @bonus=500
PRINT @bonus

DATABASE PROGRAMMING ON SQL SERVER 9

 Short introduction to T-SQL programming
CASE ... WHEN Statement
◦ Syntax
CASE input_expression
WHEN  when_expression  THEN result_expression 
[WHEN when_expression  THEN result_expression…n]
[ELSE else_result_expression ]
END

◦ Example
DECLARE @depNum DECIMAL, @str NVARCHAR(30)
SET @str=
CASE @depNum
WHEN 1 THEN N'Phòng ban số 1'
WHEN 2 THEN N'Phòng ban số 2'
ELSE N'Mã phòng ban khác 1, 2'
END
PRINT @str

DATABASE PROGRAMMING ON SQL SERVER 10

 Short introduction to T-SQL programming

We use CASE in statements such as SELECT, UPDATE, DELETE

and SET, and in clauses such as SELECT list, IN, WHERE, ORDER
BY, and HAVING

DECLARE @womanDayBonus DECIMAL

SELECT @womanDayBonus =
CASE empSex
WHEN 'F' THEN 500
WHEN ‘M' THEN 0
END
FROM tblEmployee
WHERE empSSN=30121050004

PRINT @womanDayBonus

DATABASE PROGRAMMING ON SQL SERVER 11

 Short introduction to T-SQL programming
WHILE Statement : repeats a statement or block of statements as
long as a specified condition remains true
◦ Syntax
WHILE boolean_expression 
SQL_statement | block_of_statements
[BREAK] 
SQL_statement | block_of_statements
[CONTINUE]

DECLARE @factorial INT, @n INT

◦ Example
SET @n=5
SET @factorial=1
WHILE (@n > 1)
BEGIN
SET @factorial = @factorial*@n
SET @n = @n - 1
END
PRINT @factorial

DATABASE PROGRAMMING ON SQL SERVER 12

 Short introduction to T-SQL programming

Handling error using @@ERROR function

◦ The @@ERROR system function returns 0 if the last Transact-SQL statement
executed successfully; if the statement generated an error, @@ERROR returns the
error number
BEGIN TRANSACTION
INSERT INTO tblDepartment(depNum,depName)
VALUES(6, N'Phòng Kế Toán');

INSERT INTO tblDepartment(depNum,depName)

VALUES(6, N'Phòng Kế Toán');

IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION
PRINT @@ERROR
END
COMMIT TRANSACTION

DATABASE PROGRAMMING ON SQL SERVER 13

 Short introduction to T-SQL programming

Handling error using TRY … CATCH

◦ was introduced with SQL Server 2005. Statements to be tested for an error are
enclosed in a BEGIN TRY…END TRY block. A CATCH block immediately follows
the TRY block, and error-handling logic is stored here
BEGIN TRANSACTION --begin transaction
BEGIN TRY
--oparations
INSERT INTO tblDepartment(depNum,depName)
VALUES(6, N'Phòng Kế Toán');

INSERT INTO tblDepartment(depNum,depName)

VALUES(6, N'Phòng Kế Toán');
COMMIT TRANSACTION --commit the transaction
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION --rollback transaction
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
END CATCH
DATABASE PROGRAMMING ON SQL SERVER 14
 Branching Statements

If statement
IF <condition> THEN
◦ Ends with keyword END IF
<statement list>
◦ If-statement nested within the
ELSEIF <condition> THEN
else-clause are introduced with <statement list>
the single word ELSEIF
ELSEIF
…
ELSE
<statement list>
END IF;

DATABASE PROGRAMMING ON SQL SERVER 15

 Queries in T-SQL programming
Several ways that select-from-where queries are
used in PSM
 Subqueries can be used in conditions, or in
general, any place a subquery is legal in SQL
 Queries that return a single value can be used
as the right sides of assignment statements
 A single-row select statement is a legal
statement in PSM
 We can declare and use a cursor for embedded
SQL

DATABASE PROGRAMMING ON SQL SERVER 16

 Loops in T-SQL programming
The basic loop construct in PSM is
LOOP
<statement list>
END LOOP;
It is possible to break out of the loop
LEAVE <loop label>;

Example
loop1: LOOP
...
LEAVE loop1;
...
END LOOP;

DATABASE PROGRAMMING ON SQL SERVER 17

Other Loop Constructs

WHILE <condition> DO
<statement list>
END WHILE;

REPEAT <statement list>

UNTIL <condition>
END REPEAT;

DATABASE PROGRAMMING ON SQL SERVER 18

 Exceptions in T-SQL programming
The form of a handler declaration is
DECLARE <where to go next> HANDLER FOR
<condition list> <statement list>;

The choices for where to go next

◦ CONTINUE
◦ EXIT
◦ UNDO

DATABASE PROGRAMMING ON SQL SERVER 19

 The three – tier Architecture
A very common architecture for large database
installation
Three different, interacting functions
◦ Web servers
◦ Application servers
◦ Database servers
The processes can run on the same processor or
on a large number of processors

DATABASE PROGRAMMING ON SQL SERVER 20

 The three – tier Architecture

Database

Database Database
Server Server

Application Application Application

Server Server Server

Web Web Web Web

Server Server Server Server

Internet
Client Client

DATABASE PROGRAMMING ON SQL SERVER 21

 The Webserver Tier
The webserver processes manage the
interactions with the user
When a user makes contact, a webserver
response to the request, and the user becomes a
client of this webserver process

DATABASE PROGRAMMING ON SQL SERVER 22

 The Application Tier
Turning data from the database into a response
to the request that it receives from the webserver
One webserver process invoke many application-
tier processes, which can be on one or many
different machines
The application-tier processes execute the
business logic of the organization operating the
database

DATABASE PROGRAMMING ON SQL SERVER 23

 The Database Tier
There can be many processes in the database
tier
The processes can be in one or many machines
The database tier executes queries that are
requested from the application tier

DATABASE PROGRAMMING ON SQL SERVER 24

 Advantages of using Stored Procedure
Using stored procedures offer numerous advantages over
using SQL statements. These are:
◦ Reuse of Code

◦ Maintainability

◦ Reduced Client/Server Traffic

◦ Precompiled Execution

◦ Improved Security

DATABASE PROGRAMMING ON SQL SERVER 25

 Stored procedure - Introduction
Persistent, Stored Modules (SQL/PSM)
Help to write procedures in a simple, general-
purpose language and to store them in the
database
We can use these procedures in SQL queries
and other statements to perform computations
Each commercial DBMS offers its own extension
of PSM

DATABASE PROGRAMMING ON SQL SERVER 26

 Creating Stored Procedure
under MS SQL Server
Create stored procedure:
CREATE PROCEDURE procedure_name
[ {@parameter1 data_type} [= default] [OUTPUT] ]
[ {@parameter2 data_type} [= default] [OUTPUT] ]
...
AS
sql_statement1
sql_statement2

Calling stored procedure

EXEC procedure_name [argument1, argument2,
…]
DATABASE PROGRAMMING ON SQL SERVER 27
Creating PSM Functions and Procedures

Example 1:
◦ Create stored procedure to list all projects
◦ Create stored procedure to change the project’s
name
◦ Create stored function to return the name of
project

DATABASE PROGRAMMING ON SQL SERVER 28

 Example

DATABASE PROGRAMMING ON SQL SERVER 29

 Example

DATABASE PROGRAMMING ON SQL SERVER 30

 Function in SQL Server
System Defined Function
User Defined Function
 Scalar functions
 Inline table-valued functions
 Multi-statement table-valued functions

DATABASE PROGRAMMING ON SQL SERVER 31

 Scalar functions

Calling a Function in SQL Server

//demo

DATABASE PROGRAMMING ON SQL SERVER 32

Inline Table-valued Function

//demo

DATABASE PROGRAMMING ON SQL SERVER 33

Multi-Statement Table Valued Function

//demo

DATABASE PROGRAMMING ON SQL SERVER 34

 Triggers
Triggers differ from the other constraints
◦ Triggers are only awakened when certain
events occur (INSERT, UPDATE, DELETE)
◦ One awakened, the trigger tests a condition.
◦ If the condition does not hold, trigger do nothing to
response to occurred event
◦ If the condition is satisfied, the action associated with
trigger is performed by the DBMS

DATABASE PROGRAMMING ON SQL SERVER 35

 Why uses triggers
Triggers can implement business rules
◦ E.g. creating a new Order when customer
checkout a shopping cart (in online
ecommerce websites)
Triggers be used to ensure data integrity
◦ E.g. Updating derived attributes when
underlying data is changed, or maintaining
summary data

DATABASE PROGRAMMING ON SQL SERVER 36

 Triggers in SQL
Some principle features of triggers
◦ The check of trigger’s condition and the action of the
trigger may be executed either on the state of database
that exists before the triggering event is itself executed
or on the state that exists after the triggering event is
executed
◦ The condition and action can refer to both old and/or
new values of tuples that were updated in the triggering
event
◦ It is possible to define update events that are limited to
a particular attribute or set of attributes
◦ Trigger executes either
◦ Once for each modified tuple
◦ Once for all the tuples that are changed in one SQL statement

DATABASE PROGRAMMING ON SQL SERVER 37

 Triggers in SQL (standard)
CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
OLD ROW AS OldTuple,
NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth > NewTuple.netWorth)
UPDATE MovieExec
SET netWorth=OldTuple.netWorth
WHERE cert#=NewTuple.cert#;
DATABASE PROGRAMMING ON SQL SERVER 38
 The Options for Trigger Design
AFTER/BEFORE
UPDATE/INSERT/DELETE
WHEN (<condition>)
OLD ROW/NEW ROW
BEGIN … END;
FOR EACH ROW/FOR EACH STATEMENT

DATABASE PROGRAMMING ON SQL SERVER 39

Implement Trigger with T-SQL

DATABASE PROGRAMMING ON SQL SERVER 40

 Implement Trigger with T-SQL
Create Trigger on MS SQL Server syntax
CREATE TRIGGER trigger_name ON TableName
{AFTER {[DELETE] [,] [INSERT] [,] [UPDATE]}
AS
sql_statement 1
sql_statement 2
…

Disable a trigger
DISABLE TRIGGER <trigger_name> ON <table_name>

Enable a trigger
ENABLE TRIGGER <trigger_name> ON <table_name>

DATABASE PROGRAMMING ON SQL SERVER 41

 Implement Trigger with T-SQL
Samples
Create the trigger raised after insert on
tblEmployee table
IF OBJECT_ID('Tr_Employee_Insert', 'TR') is not null
drop trigger Tr_Employee_Insert
go
CREATE TRIGGER Tr_Employee_Insert ON tblEmployee
AFTER INSERT
AS
RAISERROR('Insert trigger is awakened',16,1)
go

◦ Using AFTER INSERT, UPDATE to raise the trigger after INSERT

or UPDATE action

DATABASE PROGRAMMING ON SQL SERVER 42

 Implement Trigger with T-SQL
Transaction Management in Triggers
◦ A trigger is always part of the transaction that initiates it. That transaction can be
explicit (when SQL Server has executed Begin Transaction). It can also be implicit
basically (SQL Server treats each Transact-SQL statement as a separate
transaction)

CREATE TRIGGER Tr_Employee_Insert ON tblEmployee

AFTER INSERT
AS
RAISERROR('Insert trigger is awakened',16,1)
ROLLBACK TRANSACTION
go
--test
INSERT INTO tblEmployee(empSSN, empName, empSalary, depNum)
VALUES (30121050345, N'Nguyễnn Văn Tý', 10000, 1;
--not found employee whose empSSN is 30121050345
SELECT * FROM tblEmployee WHERE empSSN=30121050345

DATABASE PROGRAMMING ON SQL SERVER 43

 Implement Trigger with T-SQL
Deleted and Inserted tables
◦ When a trigger is executing, it has access to two memory-resident tables that allow
access to the data that was modified: Inserted and Deleted.
◦ These tables are available only within the body of a trigger for read-only access.
◦ The structures of the inserted and deleted tables are the same as the structure of
the table on which the trigger is defined

INSERT action UPDATE action DELETE action

new data old data
inserted deteted
data data
Inserted Table Deleted Table

DATABASE PROGRAMMING ON SQL SERVER 44

 Implement Trigger with T-SQL
Example: using Deleted and Inserted tables
IF OBJECT_ID('Tr_Employee_Insert', 'TR') is not null
drop trigger Tr_Employee_Insert
go
CREATE TRIGGER Tr_Employee_Insert ON tblEmployee
AFTER INSERT
AS
DECLARE @vEmpSSN DECIMAL, @vEmpName NVARCHAR(50)
SELECT @vEmpSSN=empSSN FROM inserted
SELECT @vEmpName=empName FROM inserted
PRINT 'new tuple:'
PRINT 'empSSN=' + CAST(@vEmpSSN AS nvarchar(11)) + '
empName=' + @vEmpName
go

--test
INSERT INTO tblEmployee(empSSN, empName, empSalary, depNum,
supervisorSSN)
VALUES (30121050345, N'Nguyễn Văn Tý', 10000, 1,
30121050037);

DATABASE PROGRAMMING ON SQL SERVER 45

 Samples
Create the trigger that refuses all under-18-year-old
employee’s insertion or update
CREATE TRIGGER Tr_Employee_Under18 ON tblEmployee
AFTER INSERT, UPDATE
AS
DECLARE @empBirthdate DATETIME, @age INT
SELECT @empBirthdate=empBirthdate
FROM inserted

SET @age=YEAR(GETDATE()) - YEAR(@empBirthdate)

IF (@age < 18)
BEGIN
RAISERROR(‘Employee is under 18 years old.
We can not sign a contact with
him/her.',16,1)
ROLLBACK TRANSACTION
END
go

DATABASE PROGRAMMING ON SQL SERVER 46

 Samples
Another method: using EXISTS
CREATE TRIGGER Tr_Employee_Under18 ON tblEmployee
AFTER INSERT, UPDATE
AS
IF EXISTS(SELECT *
FROM inserted
WHERE (YEAR(GETDATE())-YEAR(empBirthdate))<18
)
BEGIN
RAISERROR(‘Employee is under 18 years old.
We can not sign a contact with
him/her.',16,1)
ROLLBACK TRANSACTION
END
go

DATABASE PROGRAMMING ON SQL SERVER 47

 Using Cursor in MS SQL Server
1. Declare cursor
DECLARE cursor_name CURSOR FOR SELECT Statement
2. Open cursor
OPEN cursor_name
3. Loop and get values of each tuple in cursor with FETCH
statement
FETCH NEXT | PRIOR | FIRST | LAST
FROM cursor_name INTO @var1, @var2
4. Using @@FETCH_STATUS to check fetch status. The 0
value mean FETCH statement was successful.
5. CLOSE cursor_name
6. DEALLOCATE cursor_name

DATABASE PROGRAMMING ON SQL SERVER 48

 Example

DATABASE PROGRAMMING ON SQL SERVER 49

 Example

DATABASE PROGRAMMING ON SQL SERVER 50

 Example

DATABASE PROGRAMMING ON SQL SERVER 51