Database Systems
Session 18
Chapter 9 Stored Procedures
Objectives
1
Know what is a stored procedure
Know advantages of store procedures
Know how to create, alter and drop store procedures
Know how to return a relation in stored procedures
Know how to passing data to a stored procedure
Know how to return data from a stored procedure
Contents
Store procedure definition
Advantages of stored procedures
Managing Stored procedures
Stored Procedure Guideline
1. Stored procedure definition
A stored procedure is a
group of SQL statements
that acts as a single block of
code that performs a specific
task
This block of code is
assigned name and is stored
in the database in a
compiled form
Example: Stored Procedure
2. Advantages of stored procedures
DB developers write stored
procedures to perform a variety of
tasks related to database access
and managements. This eliminates
the need to write SQL statements
every time the same task is to be
repeated
Using stored procedures offers
many advantages over using SQL
statements. These are:
Improved Security
Precompiled execution
Reduced traffic
Reuse of code
2.1. Advantages of stored procedures:
Improved security
DB administrator can improve the security by
associating database privileges with stored
procedures.
A user can be given permission to execute a stored
procedure even if that user doesnt have
permission to access the tables or views
Example: Improved Security
Suppose a user doesnt have the permission to
access to the Customer_details table but
SHOW_CUSTOMERS procedure
So, he still can access the data of
CUSTOMER_DETAILS via SHOW_CUSTOMERS
procedure
2.2. Advantages of stored procedures:
Precompiled Execution
Stored procedures are compiled during the first
execution. For every subsequent execution, SQL
Server reuses this precompiled version
This reduces the time and resources required for
compilation
Example: Precompiled
Each time SQL Server executes the statement
SELECT * FROM customer_details, it takes time
for SQL Server to parse that statement first
But when executing the SHOW_CUSTOMERS
procedure, SQL Server only does the parsing once
2.3. Advantages of stored procedures:
Reduced traffic
Stored procedures help in reducing network traffic.
When SQL statements are executed individually, there is
network usage separately for execution of each statement
But when a stored procedure is executed, SQL statements
are executed together as a single unit. So, network path is
not used separately for execution of each individual
statement. This reduces network traffic
Example: Reduced traffic
Each time Client wants to execute the statement
SELECT * FROM customer_details, it must send
this statement to the Server.
Of course, we see that, the length of that statement
is longer than the length of Show_Customers
2.4. Advantages of stored procedures:
Reuse of code
Stored procedures can be used multiple
times
This eliminates the need to repetitively type
out hundreds of SQL statements every time
a similar task is to be performed
2. Managing Stored procedures
Managing Stored
Procedure
Creating Stored
Procedure
Altering Stored
Procedure
Dropping Stored
Procedure
Encrypting Stored
Procedure
2.1.Creating stored procedures
Example: Creating Stored Procedure
2.2.Altering stored procedures
Example: Altering Stored Procedure
2.3.Dropping stored procedures
2.4. Encrypting stored procedures
2.4. Encrypting stored procedures
When the stored procedures created, the text for
them is saved in the SysComments table. The
text is not stored for the execution of the stored
procedures but only so that it may be retrieved
later when the stored procedures need to be
modified
If the stored procedures are created with the
WITH ENCRYPTION then the text in
SysComments is not directly readable
Its common practice for third-party vendors to
encrypt their codes.
Example: Encrypting stored procedure
3. Stored Procedure Guideline
The definition of a stored procedure consists 2 parts:
Name, input and output parameters of the stored
procedure
Body of the stored procedure
3.1. Returning values
Values are passed to Stored
Procedures by calling program
when the stored procedures are
executed.
The procedures perform required
tasks using these values and then,
by default, returns a zero or nonzero integer.
The returned value is referred to as
a return code indicating whether or
not the procedures were
successfully executed
Instead of returning the default
return code, SQL Server allows you
to explicitly specify an integer value
to be returned by using RETURN
statement
3.1. Returning values
The RETURN statement passes control back to
the calling program. Any statement following
RETURN are not executed
When RETURN statement is used in a stored
procedure, it can not return a NULL value
Example: RETURN statement
3.2. Using Parameters
The data transferred between stored procedures and its
calling programs is called parameter
The parameters are divided into 2 types:
Input parameters: allow calling programs to pass values to a stored
procedure
Output parameters: allow a stored procedure passing values back to
the calling programs
Example: Input parameters
Example: Output parameters
The output parameters can not be of text or image
data type
The calling program must contain a variable to
receive the return value
Output parameters can be cursor place-holders
3.3. Nested stored procedures
3.4. Handling errors
We use TRY-CATCH construct to handle errors in stored
procedures
When an error is detected in an SQL statement inside TRY
block, control is passed to the CATCH block
The ERROR_MESSAGE() is a system function used to
display error messages
3.5. Using Stored Procedures in Queries
SELECT *
FROM
OPENQUERY(localhost,
SELECT * FROM customer_details')
SELECT *
FROM
OPENQUERY(localhost, EXECUTE
show_customers')
Syntax: OPENQUERY ( linked_server , 'query' )
