Stored Procedure

LEARNING GOALS

By the end of this lecture Understand about Stored Procedure in SQL

Server
students should be able to:

Using smoothly Stored Procedure and apply

to project

2
TABLE OF CONTENTS

Stored Procedure Overview

Benefit of Using SP

Stored Procedure vs. SQL Statement

Create a SP- Syntax

Exec, Update, Delete a SP

Stored Procedure Disadvantages

3
STORED PROCEDURE OVERVIEW (1/5)
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

4
STORED PROCEDURE OVERVIEW (2/5)
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:
USE AdventureWorks
GO
CREATE PROCEDURE GetImmediateManager
@employeeID INT, @managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END

5
STORED PROCEDURE OVERVIEW (3/5)
Stored procedures return data in four ways:
 Example (code java):
public static void executeStoredProcedure(Connection con) {
try {
CallableStatement cstmt = con
.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
cstmt.setInt(1, 5);
cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
cstmt.execute();
System.out.println("MANAGER ID: " + cstmt.getInt(2));
} catch (Exception e) {
e.printStackTrace();
}
}

6
STORED PROCEDURE OVERVIEW (4/5)
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 = TestReturn 3
SELECT ReturnValue=@ReturnValue

7
STORED PROCEDURE OVERVIEW (5/5)
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.

8
BENEFIT OF USING SP (1/2)
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

9
BENEFIT OF USING SP (2/2)
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.

10
STORED PROCEDURE 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

11
CREATE A SP- SYNTAX

Create / Modify a SP
CREATE PROC[EDURE] procedure_name
[@parameter_name data_type][= default] OUTPUT][,...,n]
AS
SQL_statement_block

12
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

13
STORED PROCEDURE DEMO
Demo

14
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

15
Quiz!

Now let's check how you understand

the lecture!

There are 7 questions below.

Click NEXT button to start!

16
 Quiz!
There are 7 questions below.
Click NEXT button to start!

17
SUMMARY

Stored Procedure Overview

Benefit of Using SP

Stored Procedure vs. SQL Statement

Create, Exec, Update, Delete a SP

Stored Procedure Disadvantages

18
EXIT COURSE

THANK YOU
You have completed "Lecture _03" course.

Click EXIT button to exit course and discover

the next Lecture "Lecture_04".

EXIT

19