Page 108 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Introduction to Stored Procedures

Stored procedures are a feature in SQL that allows you to define and store a set of SQL statements as a named
procedure in a database. These procedures can be called and executed by applications, users, or other database
objects. Stored procedures are commonly used for encapsulating business logic, improving code reusability, and
enhancing database security. Here’s an overview of stored procedures in SQL:

Creating a Stored Procedure
The syntax for creating a stored procedure can vary slightly between different SQL database management
systems (DBMS), but the general structure is similar. Below is a simplified example in generic SQL syntax:
CREATE PROCEDURE procedure_name ([parameter_list])

AS BEGIN
-- SQL statements to define the procedure’s logic
END;
• procedure_name: This is the name you give to your stored procedure.

• [parameter_list]: You can define input parameters that the procedure can accept. These parameters are
optional.
Example of Creating a Simple Stored Procedure in SQL Server:

CREATE PROCEDURE GetEmployee @EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;
In this example, we’ve created a stored procedure called GetEmployee that takes an EmployeeID as an input
parameter and selects the corresponding employee from the Employees table in SQL Server.
Executing a Stored Procedure
To execute a stored procedure, you can use the EXEC or CALL statement, depending on the specific SQL DBMS
you are using.
• In SQL Server, you use EXEC:
EXEC GetEmployee @EmployeeID = 123;

In MySQL, you use CALL:
CALL GetEmployee(123);
Modifying a Stored Procedure:
You can modify an existing stored procedure using the ALTER PROCEDURE statement in SQL Server, or the
ALTER PROCEDURE command in other DBMS, which can vary slightly.
Dropping a Stored Procedure:
To remove a stored procedure, you use the DROP PROCEDURE statement:
DROP PROCEDURE IF EXISTS GetEmployee;

Control Flow in Stored Procedures:
Stored procedures support various control flow constructs like IF, ELSEIF, ELSE, CASE, loops (e.g., WHILE,
LOOP, REPEAT), and exception handling using BEGIN...END blocks, depending on the specific DBMS.

Security Considerations:
You can control access to stored procedures by granting or revoking execution privileges to specific users or roles.
Properly managing permissions is essential to ensure that only authorized users can execute these procedures

95

CITS : IT&ITES - Computer software application - Lesson 18 - 36