MSSQL
Advanced SQL: Store Procedure, Function,
Trigger, Cursor in MS SQL Server
Chapter 4 (cont.)
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
2
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
3
Declaring Local Variables
? Variables are declared within the body of a batch or a
procedure using the DECLARE statement.
Example:
4
Setting Values for Variables
? If no default value is assigned, the variable is
initialized with the value null, and later it can be
assigned a value using the SET statement.
Example:
5
Conditional Structure
? Syntax:
6
Conditional Structure
? Example: The following code calculates the total sales
amount for all orders in 2011. If the total exceeds
10,000,000, it prints:
“The sales amount in 2011 is greater than 10,000,000.”
Otherwise, it prints:
“The sales amount in 2011 did not reach 10,000,000.”
7
8
Loop Structure
? Syntax
Repeats the statements as long as the condition is true.
? Example
9
Loop Structure
? BREAK: Ends the loop early when a condition is met.
10
Loop Structure
? CONTINUE: Skips the rest of the current loop iteration
and moves to the next iteration.
11
Loop Structure
? Example:
Guess the
results
12
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
13
Store procedure overview
● Stored Procedure is a group of precompiled Transact-
SQL statement into a single execution plan.
○ Accept input parameters and return multiple values in
the form of output parameters.
○ Contain programming statements that perform
operations in the database, including calling other
procedures.
○ Return a status value to indicate success or failure (and
the reason for failure).
14
Store procedure overview (cond.)
● Types of stored procedures:
○ User-defined procedures
○ Temporary procedures
○ System procedures
● Advantage:
○ Reduced server/client network traffic
○ Stronger security
○ Reuse of code
○ Easier maintenance
○ Improved performance
15
Store procedure syntax
● Create a store procedure
CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name
[ { @parameter data_type } [ VARYING ] [ = default ]
[ OUT | OUTPUT | [READONLY] ] [ ,...n ]
AS
{sql_statement}
● Execute a store procedure:
EXEC | EXECUTE [schema_name.] procedure_name
[ [ @parameter = ] { value | @variable [ OUTPUT ]
16
Store procedure example
● CREATE OR ALTER PROCEDURE Update_Sal
@p_emp_id CHAR (9), @p_factor NUMERIC(3,2)
AS
DECLARE @v_count INT;
SELECT @v_count = COUNT(*)
FROM EMPLOYEE
WHERE SSN = @p_emp_id;
IF @v_count = 1
UPDATE EMPLOYEE
SET Salary = Salary * @p_factor
WHERE SSN = @p_emp_id;
● EXEC Update_Sal ‘123456789’, 1.2;
17
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
18
Function overview
● SQL Server user-defined functions are routines:
○ accept parameters,
○ perform an action, such as a complex calculation,
○ and return the result of that action as a value.
● Types of functions:
○ Scalar Function:
■ Return a single data value of the type defined in the
RETURNS clause.
■ The return type can be any data type except text, ntext,
image, cursor, and timestamp.
○ Table-Valued Functions: return a table data type.
○ System Functions
19
Scalar function syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ]
function_name
( [ { @parameter_name [ AS ] data_type
[ = default ] [ READONLY ] } [ ,...n ] ] )
RETURNS return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END;
20
Table-valued function syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ]
function_name
( [ { @parameter_name [ AS ] data_type
[ = default ] [READONLY] } [ ,...n ] ] )
RETURNS @return_variable TABLE <table_type_definition>
[ AS ]
BEGIN
{function_body}
RETURN
END;
21
Scalar function example
Create a scalar function:
CREATE OR ALTER FUNCTION Get_Sal (@p_id CHAR(9))
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @v_sal DECIMAL(10,2);
SET @v_sal = (SELECT salary
FROM EMPLOYEE
WHERE SSN = @p_id);
RETURN @v_sal;
END;
Execute:
SELECT dbo.Get_Sal ('333445555');
22
Table-valued function example
● Create table-valued function:
CREATE FUNCTION EmpAndDependent() INSERT INTO @person
RETURNS @person TABLE (
SELECT D.Dependent_name,
first_name VARCHAR(15), [Link],
last_name VARCHAR(15), [Link], 'Dependent’
sex CHAR, FROM EMPLOYEE E, DENPENDENT
type VARCHAR(10) ) D
AS WHERE [Link] = [Link]
BEGIN
INSERT INTO @person
RETURN;
SELECT Fname, LName, Sex, 'Employee'
END;
FROM EMPLOYEE;
● Execute:
SELECT * FROM EmpAndDependent ();
23
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
24
Trigger Overview
● SQL Server triggers are special stored procedures that
are executed automatically (by the DBMS) when an
event occurs in the database server.
● SQL Server provides three type of triggers:
○ Data manipulation language (DML) triggers: invoked
automatically in response to INSERT, UPDATE, and
DELETE statements on a table or view.
○ Data definition language (DDL) triggers: fire in
response to CREATE, ALTER, and DROP statements, and
certain system stored procedures that perform DDL-like
operations.
○ Logon triggers: fire in response to LOGON events.
25
Uses of Trigger
● Automatically generate derived column values.
● Maintain complex integrity constraints.
● Enforce complex business rules.
● Record auditing information about database changes.
26
Simple DML Trigger Syntax
CREATE [ OR ALTER ] TRIGGER [schema.] trigger_name
ON { table_name | view_name }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
{sql_statements}
27
Trigger Firing Order
1. INSTEAD OF trigger.
2. Constraints exist on the trigger table.
3. AFTER trigger runs.
● If the constraints are violated, the INSTEAD OF trigger
actions are rolled back and the AFTER trigger isn't
fired.
28
“Virtual” tables for triggers
● Two “virtual” tables that are available specifically for
triggers called INSERTED and DELETED tables.
○ SQL Server uses these tables to capture the data of the
modified row before and after the event occurs.
DML event INSERTED table DELETED table
INSERT rows to be inserted empty
new rows modified by the existing rows modified by
UPDATE
update the update
DELETE empty rows to be deleted
29
Trigger Example
CREATE OR ALTER TRIGGER CHECK_AGE
ON Employee FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM INSERTED
WHERE DATEADD(YEAR, 18, BDate) > GETDATE())
BEGIN
ROLLBACK;
THROW 51000, 'Employee age must be greater than or
equal to 18.',1;
END
30
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
31
Cursor overview
● A SQL cursor is a database object that is used to
retrieve data from a result set one row at a time.
● Why use a SQL Cursor?
○ In relational databases, operations are made on a set of
rows. For example, a SELECT statement returns a set of
rows which is called a result set.
○ Sometimes we may want to process a data set on a row
by row basis rather than the entire result set at once.
○ Using cursors.
32
Cursor life cycle
DECLARE OPEN FETCH
NO
EMPTY?
YES
DEALLOCATE CLOSE
33
Cursor syntax
● Declare a cursor:
DECLARE cursor_name CURSOR
FOR {SELECT statements}
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] }
]
● Open a cursor:
OPEN Cursor_name
● Close a cursor:
CLOSE Cursor_name
● Deallocate a cursor: Removes a cursor reference
○ DEALLOCATE Cursor_name
34
Cursor syntax (cond.)
Statement Description
FETCH [NEXT| PRIOR | FIRST |LAST]
FROM Cursor_name [INTO Var_list]
• FETCH NEXT: Returns the result row immediately
following the current row.
FETCH
• FETCH PRIOR: Returns the result row immediately
preceding the current row.
• FETCH FIRST: Returns the first row in the cursor.
• FETCH LAST: Returns the last row in the cursor.
Returns the number of rows currently in the opened
@@CURSOR_ROWS
cursor.
@@FETCH_STATUS Returns the status of the last cursor FETCH statement
Shows whether or not a cursor declaration has
CURSOR_STATUS
returned a cursor and result set.
35
Cursor example
CREATE PROCEDURE PrintEmployee_Cursor --loop until records are available.
AS BEGIN WHILE @@FETCH_STATUS = 0
--declare the variables BEGIN
DECLARE @v_empID INT, IF @v_counter = 1
@v_name VARCHAR(100) PRINT 'EmployeeSSN' + CHAR(9) + 'Name’
--declare and set counter. --print current record.
DECLARE @v_counter INT PRINT CAST (@v_empID AS VARCHAR(9))
SET @v_counter = 1 + CHAR(9) + @v_name
--declare the cursor for a query. --increment counter.
DECLARE EmployeeCursor CURSOR SET @v_counter = @v_counter + 1
FOR SELECT SSN, FName + ' ' + LName --fetch the next record
FROM Employee FETCH NEXT FROM EmployeeCursor
--open cursor. INTO @v_empID, @v_name
OPEN EmployeeCursor END
--fetch the record --close the cursor.
FETCH NEXT FROM EmployeeCursor CLOSE EmployeeCursor
INTO @v_empID, @v_name DEALLOCATE EmployeeCursor
36
END;
Contents
1 Basic syntax
2 Store procedure
3 Function
4 Trigger
5 Cursor
6 Error Handling
37
Error Handling
● THROW statement: THROW [error_number, message, state];
■ error_number:
● A constant or variable that represents the exception.
● The error_number argument is INT.
● Must be greater than or equal to 50,000, and less than or equal to 2,147,483,647.
■ message:
● A string or variable that describes the exception.
● The message argument is NVARCHAR(2048).
■ state:
● A constant or variable between 0 and 255 that indicates the state to associate with
the message.
● The state argument is TINYINT.
THROW 50001, 'Invalid student ID', 1;
38
Error Handling
● RAISERROR statement:
RAISERROR (message_string | message_id, severity, state [, argument1,
argument2, ...]) [WITH option [, ...]]
39
Common Severity Levels
WITH Options
40
Example
RAISERROR('Invalid student ID', 16, 1);
-- Raise the error with message id defined
EXEC sp_addmessage
@msgnum = 50001,
@severity = 16,
@msgtext = 'Student ID %d not found in database.';
RAISERROR(50001, 16, 2, 1001);
-- Raise the error with SETERROR
RAISERROR('Invalid student ID', 16, 1) WITH SETERROR;
IF @@ERROR <> 0
PRINT 'An error occurred!';
41
Try… Catch
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
[;]
42
Retrieving Error Details
● The following system functions can be used to obtain information
about the error that caused the CATCH block to be executed :
○ ERROR_NUMBER: Returns the number of the error.
○ ERROR_SEVERITY: Returns the severity.
○ ERROR_STATE: Returns the error state number.
○ ERROR_PROCEDURE: Returns the name of the stored procedure or trigger
where the error occurred.
○ ERROR_LINE: Returns the line number inside the routine that caused the
error.
○ ERROR_MESSAGE: Returns the complete text of the error message. The text
includes the values supplied for any substitutable parameters, such as
lengths, object names, or times.
43
Try… Catch Example (1)
CREATE PROCEDURE InsertDept @DNumber INT, @DName VARCHAR(20),
@MgrSSN CHAR(9), @MgrStartDate DATE
AS BEGIN
BEGIN TRY
INSERT INTO DEPARTMENT
VALUES (@DName, @DNumber, @MgrSSN, @MgrStartDate);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(255);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
44
Try… Catch Example (2)
BEGIN TRY
DECLARE @studentID INT = NULL;
IF @studentID IS NULL
THROW 50010, 'Student ID cannot be NULL.', 1;
END TRY
BEGIN CATCH
PRINT 'Caught custom error:';
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_STATE() AS ErrorState;
END CATCH;
45
Try… Catch Example (3)
BEGIN TRY
DECLARE @x INT = 10 / 0; -- lỗi chia cho 0
END TRY
BEGIN CATCH
PRINT 'Error caught in CATCH block.';
THROW; -- ném lại lỗi gốc ra ngoài
END CATCH;
46
Using Throw in Try … catch
47
Using RAISEERROR in Try … catch
48
Key Differences between RAISERROR and
THROW
RAISERROR statement THROW statement
If a msg_id is passed to RAISERROR, the The error_number parameter doesn't
ID must be defined in [Link]. have to be defined in [Link].
The msg_str parameter can The message parameter doesn't
contain printf formatting styles. accept printf style formatting.
The severity parameter specifies the There's no severity parameter.
severity of the exception. When THROW is used to initiate the
exception, the severity is always set
to 16.
However, when THROW is used to
rethrow an existing exception, the
severity is set to that exception's
severity level.
49
50
Exercise
1. Write a trigger for ensuring that the employee’s ages
must be between18 and 60.
2. Write a trigger to enforce that when an employee has a
new project, his or her salary will be increased by 1% *
number of hours per week working on that project.
3. Write a store procedure to list an employee’s id and the
names of his/her dependents.
4. Write a function to read a project’s id and return the total
number of employees who work for that project.
Employees(EmpID, Name, Salary, Age)
EmployeeProjects(EmpID, ProjectID, HoursPerWeek)
Dependents(EmpID, DependentName, Relationship):
51
Exercise 01.
CREATE TRIGGER trg_Employee_Age_Check
ON Employees
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT 1
FROM inserted
WHERE Age < 18 OR Age > 60
)
BEGIN
RAISERROR('Employee age must be between 18 and 60.', 16, 1);
ROLLBACK TRANSACTION;
END
END;
52
Exercise 02
CREATE TRIGGER trg_Employee_Project_Salary_Cursor
ON EmployeeProjects
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
FETCH NEXT FROM cur INTO @EmpID, @Hours;
DECLARE @EmpID INT, @Hours INT;
WHILE @@FETCH_STATUS = 0
-- Cursor to loop through each inserted row
BEGIN
DECLARE cur CURSOR FOR
-- Update salary directly for this employee
SELECT EmpID, HoursPerWeek
UPDATE Employees
FROM inserted;
SET Salary = Salary + 0.01 * @Hours * Salary
OPEN cur;
WHERE EmpID = @EmpID;
FETCH NEXT FROM cur INTO @EmpID, @Hours;
END
CLOSE cur;
DEALLOCATE cur;
END;
53
Exercise 03
CREATE OR ALTER PROCEDURE Relationship
GetEmployeeDependents FROM Dependents
@EmpID INT WHERE EmpID = @EmpID;
AS
BEGIN END;
IF NOT EXISTS (SELECT 1 FROM
Dependents WHERE EmpID = @EmpID) -- Execute the stored procedure with
BEGIN example EmpID
SELECT 'No dependents found for EXEC GetEmployeeDependents @EmpID =
the specified Employee ID.' AS Message; 11;
RETURN;
END;
SELECT DependentName,
54
Exercise 04
CREATE FUNCTION GetProjectEmployeeCount
(
@ProjectID INT
)
RETURNS INT
AS
BEGIN
DECLARE @Count INT;
SELECT @Count = COUNT(EmpID)
FROM EmployeeProjects
WHERE ProjectID = @ProjectID;
RETURN @Count;
END;
-- Usage
SELECT [Link](101) AS NumEmployees;
55
-- =============================== INSERT INTO Employees (EmpID, Name, Age, Salary)
-- 1️⃣ Create Tables VALUES
-- =============================== (1, 'Alice', 25, 5000),
(2, 'Bob', 35, 6000),
-- Employees table (3, 'Charlie', 45, 5500),
CREATE TABLE Employees ( (4, 'David', 17, 6500); -- intentionally < 18 to test age
EmpID INT PRIMARY KEY, trigger
Name NVARCHAR(50),
Age INT, INSERT INTO Dependents (EmpID, DependentName,
Salary DECIMAL(10,2) Relationship)
); VALUES
(1, 'Tom', 'Son'),
-- Dependents table (1, 'Anna', 'Daughter'),
CREATE TABLE Dependents ( (2, 'John', 'Spouse');
DependentID INT PRIMARY KEY IDENTITY(1,1),
EmpID INT, INSERT INTO EmployeeProjects (EmpID, ProjectID,
DependentName NVARCHAR(50), HoursPerWeek)
Relationship NVARCHAR(20), VALUES
FOREIGN KEY (EmpID) REFERENCES (1, 101, 10),
Employees(EmpID) (2, 102, 20);
);
-- ===============================
-- EmployeeProjects table
CREATE TABLE EmployeeProjects (
EmpID INT,
ProjectID INT,
HoursPerWeek INT,
PRIMARY KEY (EmpID, ProjectID),
FOREIGN KEY (EmpID) REFERENCES
Employees(EmpID)
);
-- ===============================
-- 2️⃣ Insert Sample Data
56
-- ===============================