User Stored Procedures

and
User-Defined Functions
Fsoft Academy

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use

Lesson Objectives

Understand about
This isUSP, UDF
a sample andyouSQL
text that Code
can edit.
01 You can change font(size, color, name),
Practice in SQL Server.
or apply any desired formatting.

02 Using smoothly them and apply to project.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 2

Agenda

1. Basic of programming SQL

2. User Defined Functions

3. User Stored Procedures

3.
3. Demo

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 3

Section 1

Basic of Programming SQL

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 4

Comments
 Indicate user-provided text
• Double Dash:
• Ex: SELECT * FROM Orders -- This is a comment

• Block Comment:
• Ex: /*Multi-line comments here*/

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 5

Identifiers
The database object name is referred to as its identifier.
 An object identifier is created when the object is defined
 The identifier is used to reference the object

There are 2 types of Identifiers:

 Regular Identifiers:
• Example: Orders, Customers, Employee…

 Delimited Identifiers: Are enclosed in double quotation marks (") or brackets ([ ])

• [My Table]

• [1Person]

 For Example:
SELECT * FROM [My Table]
WHERE [Order]=40

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 6

Variables
Declare a variable
Must be DECLARE and start with @ symbol
DECLARE @limit money
DECLARE @min_range int, @hi_range int

 Assign a value into a variable using SET

SET @min_range = 0, @hi_range = 100
SET @limit = $10

 Assign a value into a variable using SELECT

SELECT @price = price FROM titles
WHERE title_id = 'PC2091'

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 7

Control-of-flow
The T-SQL control-of-flow language keywords are:
BEGIN…END
IF…ELSE
CASE … WHEN
TRY…CATCH
WHILE
BREAK / CONTINUE
GOTO
RETURN

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 8

Control-of-flow/BEGIN…END
 BEGIN…END
 Define a statement block

 Other Programming Languages:

• C#, Java, C: {...}

• Pascal, Delphi: BEGIN ... END

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 9

Control-of-flow/IF…ELSE
 IF…ELSE
 Define conditional and, optionally, alternate execution when a condition is false
 Syntax:
IF Boolean_expression
SQL_statement|block_of_statements
[ELSE
SQL_statement|block_of_statements]
 Example:

USE AdventureWorks2022;
GO

IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5

PRINT 'There are more than 5 Touring-3000 bicycles.'
ELSE
PRINT 'There are 5 or less Touring-3000 bicycles.' ; GO

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 10

Control-of-Flow
 CASE … WHEN
 Evaluate a list of conditions and returns one of multiple possible result
expressions
 Syntax:

CASE input_expression
WHEN when_expression1 THEN result_expression1
WHEN when_expression2 THEN result_expression2
...
ELSE else_result_expression
END

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 11

Control-of-Flow
 CASE … WHEN
USE AdventureWorks2022;

GO

SELECT ProductNumber,
Category = CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale' END,
Name
FROM Production.Product
ORDER BY ProductNumber;

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 12

Control-of-flow
 TRY… CATCH
 Provide error handling for T-SQL that is similar to the exception handling in the C# /
Java
 Syntax:

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 13

Control-of-flow
 WHILE
Set a condition for the repeated execution of an 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
 Syntax
WHILE Boolean_expression
{ sql_statement | statement_block | BREAK |
CONTINUE }

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 14

BREAK / CONTINUE
 The WHILE statement repeats a statement or block of statements as long as a specified condition
remains true.
 Two Transact-SQL statements are commonly used with WHILE: BREAK or CONTINUE.
 The BREAK statement exits the innermost WHILE loop and the CONTINUE statement restarts a WHILE loop.
 A program might execute a BREAK statement if, for example, there are no other rows to process. A CONTINUE
statement could be executed if, for example, the execution of the code should continue.
 Example:
USE AdventureWorks2022;

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300

BEGIN
UPDATE Production.Product SET ListPrice = ListPrice * 2

SELECT MAX(ListPrice) FROM Production.Product

IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 15

Control-of-flow
 GOTO
 Alter the flow of execution to a label. The Transact-SQL statement or statements that
follow GOTO are skipped and processing continues at the label

 Syntax:

--Define the label

label :

--Alter the execution:

GOTO label

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 16

Control-of-flow
 RETURN
 Exit unconditionally from a query or procedure

 This will be discussed more details in Stored Procedure section.

 Syntax

RETURN [ integer_expression ]

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 17

Section 2

Stored Procedures

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 18

Overview
 A stored procedure (SP) is a collection of SQL statements that SQL
Server compiles into a single execution plan.
 It can accept input parameters, return output values as parameters, or
return success or failure status messages.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 19

Types of Stored Procedures
 User-defined
 A user-defined procedure can be created in a user-defined database or in
all system databases except the Resource database.
 Temporary
 Temporary procedures are a form of user-defined procedures. The
temporary procedures are like a permanent procedure, except temporary
procedures are stored in tempdb.
 There are two types of temporary procedures: local and global. They differ
from each other in their names, their visibility, and their availability.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 20

Types of Stored Procedures
 System
 System procedures are included with SQL Server.
 They are physically stored in the internal, hidden Resource database
and logically appear in the sys schema of every system- and user-
defined database.

 Because system procedures start with the prefix sp_, we recommend that
you do not use this prefix when naming user-defined procedures.
 In this topic we will learn about User Stored Procedure.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 21

Create a SP- Syntax
 Basic Syntax to Create a SP:
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

GO

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 22

User Stored Procedures
 Stored procedures return data in four ways:
 Output parameters, which can return either data (such as an integer or character
value) or a cursor variable (cursors are result sets that can be retrieved one row at a
time).
 Example 1:
USE AdventureWorks2022;

GO
CREATE PROCEDURE GetImmediateManager
@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 23

User Stored Procedures
 Example 2:
USE AdventureWorks2022;
GO

CREATE PROCEDURE HumanResources.uspGetEmployeesTest

@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON;

SELECT FirstName, LastName, Department

FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL;
GO

 Run the procedure:

EXECUTE/EXEC HumanResources.uspGetEmployeesTest N'Ackerman', N'Pilar';

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 24

User Stored Procedures
 Stored procedures return data in four ways:
 Return codes, which are always an integer value.
 Example:
CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue + 10
GO

-- Call SP
DECLARE @ReturnValue INT

EXEC @ReturnValue = dbo.TestReturn 3

SELECT ReturnValue=@ReturnValue
 Result:

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 25

User Stored Procedures
 Stored procedures return data in four ways:
A result set for each SELECT statement contained in the stored procedure or
any other stored procedures called by the stored procedure.
A global cursor that can be referenced outside the stored procedure.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 26

Benefit of Using SP
 Benefit of Using SP
Reduced server/client network traffic: Only the call to execute the procedure is
sent across the network

 Stronger security: When calling a procedure over the network, only the call to
execute the procedure is visible. Therefore, malicious users cannot see table
and database object names, embed Transact-SQL statements of their own, or
search for critical data
09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 27
Benefit of Using SP
 Benefit of Using SP
Reuse of code:
• The code for any repetitious database operation is the perfect candidate for
encapsulation in procedure (for instance, UPDATE data on a table)
Improve Performance:
• Procedure is stored in cache area of memory when the stored procedure is first
executed so that it can be used repeatedly. SQL Server does not have to
recompile it every time the stored procedure is run.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 28

Stored Procedures vs. SQL Statement
SQL Statement Stored Procedure
Creating
- Check syntax
- Compile

First Time First Time

- Check syntax - Execute
- Compile - Return data
- Execute
- Return data

Second Time Second Time

- Check syntax - Execute
- Compile - Return data
- Execute
- Return data

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 29

Exec, Update, Delete a SP
 Execute a Procedure:
EXEC[UTE] procedure_name

 Update a Procedure
ALTER PROC[EDURE] procedure_name
[ @parameter_name data_type] [= default] [OUTPUT]
[,...,n]
AS
SQL_statement(s)

 Delete a Procedure
DROP PROC[EDURE] procedure_name

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 30

Stored Procedure Disadvantages
 Make the database server high load in both memory and processors
 Difficult to write a procedure with complexity of business logic
 Difficult to debug
 Not easy to write and maintain

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 31

Section 3

User-Defined Functions

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 32

What is a UDF?
 User-Defined Function (UDF) are routines that accept parameters, perform an
action and return the result of that action as a value.
 The return value can be a single scalar value or a result set
 Functions are computed values and cannot perform permanent environmental
changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 33

Benefits of User-Defined Functions
Why use user-defined functions (UDFs)?
 Modular programming:
 You can create the function once, store it in the database, and call it any number of times in your
program.
 User-defined functions can be modified independently of the program source code.
 Faster execution
 Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost
of Transact-SQL code by caching the plans and reusing them for repeated executions.
 This means the user-defined function doesn't need to be reparsed and reoptimized with
each use resulting in much faster execution times.

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 34

Benefits of User-Defined Functions
Why use user-defined functions (UDFs)?
 Reduce network traffic.
 An operation that filters data based on some complex constraint that can't be expressed in a
single scalar expression can be expressed as a function.
 The function can then be invoked in the WHERE clause to reduce the number of rows sent to
the client.

!Important
Transact-SQL UDFs in queries can only be executed on a single thread (serial execution plan).

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 35

UDF’s types
 Scalar User-Defined Function: can accept zezo to many input parameter
and will return a single value:
• A Scalar user-defined function returns one of the scalar (int, char, varchar etc) data types
• Text, ntext, image, cursor and timestamp data types are not supported
 Table-valued functions
• Inline Table-valued Functions: returns a variable of data type table whose value is derived from
a single SELECT statement
• Multi-statement Table-Valued Functions: returns a table

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 36

UDF Function
 UDF Scalar Function Syntax

CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name data_type [ = default ] [ READONLY ] } [ ,...n ] ] )

RETURNS return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 37

UDF Function
 UDF Scalar Function Example
CREATE FUNCTION sales.udfNetSale(
@quantity INT,
@list_price DEC(10,2),
@discount DEC(4,2)
)
RETURNS DEC(10,2)
AS
BEGIN
RETURN @quantity * @list_price * (1 - @discount);
END;

 Calling a scalar function  Output:

SELECT sales.udfNetSale(10,100,0.1) net_sale;

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 38

UDF Function
 UDF Inline Table-valued Functions Syntax

CREATE FUNCTION [schema_name.]function_name

( [ { @parameter_name data_type [ = default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_statement [ ) ]

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 39

UDF Function
 UDF Inline Table-valued Functions Example Product table:
CREATE FUNCTION [dbo].[udfGetProductList](
@SafetyStockLevel SMALLINT
)
RETURNS TABLE
AS
RETURN
(SELECT Product.ProductID,
Product.Name,
Product.ProductNumber
FROM Production.Product
WHERE SafetyStockLevel >= @SafetyStockLevel)

 Run the function:

SELECT * FROM dbo.udfGetProductList( 100 )

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 40

UDF Functions Syntax
 UDF Multi-statement Table-Valued Functions:
 A multi-statement table-valued function or MSTVF is a table-valued function that
returns the result of multiple statements.
 The multi-statement-table-valued function is very useful because you can execute
multiple queries within the function and aggregate results into the returned table.

CREATE FUNCTION [ schema_name. ] function_name

(( [ { @parameter_name data_type [ = default ] [ READONLY ] } [ ,...n ] ] ))

RETURNS @return_variable TABLE <table_type_definition>

[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 41

UDF Functions Syntax
 UDF Multi-statement Table-Valued Functions Example:
CREATE FUNCTION udf_Contacts()
RETURNS @contacts TABLE (
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(255),
phone VARCHAR(25),
contact_type VARCHAR(20) )
AS
BEGIN
INSERT INTO @contacts
SELECT first_name, last_name, email, phone,'Staff'
FROM sales.staffs;

INSERT INTO @contacts

SELECT first_name, last_name, email, phone,'Customer'
FROM sales.customers;
RETURN;
END;

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 42

UDF Functions Syntax
 Execute a multi-statement table-valued function:
SELECT * FROM udfContacts();

 Output:

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 43

User-Defined Function Demo
 Demo
 Scalar function
 Inline table function
 Multi-statement table-valued function

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 44

SUMMARY
 Basic of Programming SQL
 User-Defined Function
What is a Function?
UDF’s Type
UDF Function Syntax
 User Stored Procedures

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use 45

THANK YOU!

09e-BM/DT/FSOFT - @FPT SOFTWARE - FPT Software Academy - Internal Use