Definition of Stored Procedure (DSP)

AVAILABLE:    5

A stored procedure is a collection of SQL commands saved under a specific name and processed as a single unit. Stored procedures are similar to procedures or functions in programming languages that can be executed independently. The similarities include:

• They can accept parameters as input and return values as output parameters to the caller.
• They contain a set of program instructions that perform operations within the database, including calling other procedures.
• They return a value to the caller to indicate the success or failure of the procedure and the reason for any failure.

Understanding Stored Procedures

The advantages of using stored procedures are:

1. More Modular Programming
   Stored procedures only need to be created once, stored in the database, and can be called multiple times from programming languages such as Delphi, VFP, PHP, Java, or VB.

2. Easier Modification
   Stored procedures can be modified without having to change the program code (e.g., in Delphi, Java, VFP, or PHP) that calls them.

3. Faster Execution
   Execution is faster since stored procedures are precompiled.

4. Reduced Network Traffic
   This is especially beneficial in client/server applications, as only the stored procedure is sent over the network instead of sending multiple lines of SQL commands.

5. Enhanced Security Mechanism
   Users can be granted the right to execute a stored procedure even if they don't have access to the SQL commands within the procedure.

Rules for Creating Stored Procedures

1. Stored procedures can only be created in the active database.
2. The name of the stored procedure must follow the rules for naming variables.
3. To execute a stored procedure, use the EXECUTE or EXEC command.

Basic Command for Creating Stored Procedures:

CREATE PROCEDURE procedure_name
@parameter1 datatype,
@parameter2 datatype,
.......
.......
OUTPUT
AS
SQL_commands

1. Stored Procedure Without Parameters

Below is an example of creating a stored procedure without parameters:

CREATE PROCEDURE sp_nasabah
AS
SELECT idnasabah AS 'ID', nama_nasabah AS 'Full Name',
alamat AS 'Address'
FROM nasabah

To execute the stored procedure, type the following command:

EXEC sp_nasabah

2. Stored Procedure With Parameters

Below is an example of creating a stored procedure with a parameter based on the branch code (kodecabang):

CREATE PROCEDURE sp_transaksi
@kodecabang VARCHAR(25)
AS
SELECT nasabah.nama_nasabah, cabang_bank.nama_cabang
FROM nasabah, cabang_bank, nasabah_rek
WHERE nasabah.idnasabah = nasabah_rek.idnasabah
AND rekening.norek = nasabah_Rek.norek
AND cabang_bank.kodecabang = @kodecabang

To display all customers with accounts in the branch "BPR Cahaya Mitra," use the following command. The @kodecabang parameter is set to 'BPR Cahaya Mitra', but you can replace it with the desired branch name:

EXEC sp_transaksi 'BPR Cahaya Mitra'

Below is an example of creating a stored procedure where a default parameter value is provided, but it can still accept a different parameter value if specified:

CREATE PROCEDURE sp_trans
@kodecabang VARCHAR(25) = 'BPR Cahaya Madani'
AS
SELECT nasabah.nama_nasabah, cabang_bank.nama_cabang
FROM nasabah, cabang_bank, nasabah_rek
WHERE nasabah.idnasabah = nasabah_rek.idnasabah
AND rekening.norek = nasabah_Rek.norek
AND rekening.kode_cabang = cabang_bank.kode_cabang
AND cabang_bank.kodecabang = @kodecabang

To see the results, execute the following command:

EXEC sp_trans

Hope this is useful, and happy learning!