13. Stored Procedures & Functions

Why Stored Procedures Exist

There are four concrete reasons why production systems use stored procedures instead of raw SQL strings.

Security. When an application calls a stored procedure, it passes parameter values but never sends raw SQL text. This eliminates the most common web vulnerability — SQL Injection — because user input is treated as a data value, never as executable code.

Performance. The first time a stored procedure runs, SQL Server compiles it, optimises its execution plan, and caches that plan in memory. Every subsequent call reuses the cached plan. A raw SQL string sent from an application may be re-optimised every time, wasting processing time.

Maintainability. Business logic lives in one place — the database. If the logic changes, you update the stored procedure once. Every application that calls it immediately benefits without requiring a code deployment.

Abstraction. Applications do not need to know table names, column names, or query structure. They simply call a procedure by name and receive results. The internal implementation can change without affecting the application interface.

Creating a Stored Procedure in SQL Server

In SSMS, open a New Query window and ensure you are in VoidX_Academy. The syntax for creating a stored procedure in SQL Server uses CREATE PROCEDURE followed by the procedure name and parameter definitions.

Let us create a simple procedure that retrieves all students in a specific department. The department ID will be passed as a parameter each time the procedure is called:

CREATE PROCEDURE GetStudentsByDept
  @DeptID INT
AS
BEGIN
  SELECT
    StudentID,
    FirstName,
    LastName,
    GPA
  FROM Student
  WHERE DeptID = @DeptID
  ORDER BY LastName;
END;

Every parameter in a SQL Server stored procedure starts with the @ symbol. The data type INT follows immediately. The AS keyword separates the parameter definitions from the procedure body. BEGIN and END mark the boundaries of the procedure body — everything between them is the code that runs when the procedure is called.

Press F5 to create the procedure. You will see it appear in Object Explorer under your database, inside the Programmability folder, then Stored Procedures.

Executing a Stored Procedure

Call a stored procedure using the EXEC keyword followed by the procedure name and the parameter value:

EXEC GetStudentsByDept @DeptID = 1;

This returns all students in Department 1. To see students in Department 2, call it again with a different value — the logic does not change, only the input:

EXEC GetStudentsByDept @DeptID = 2;

The procedure runs instantly because its execution plan was compiled and cached when you first created it.

Stored Procedures with Multiple Parameters

Procedures can accept any number of parameters. Here is a procedure that inserts a new student, accepting all required values as parameters:

CREATE PROCEDURE AddStudent
  @FirstName VARCHAR(50),
  @LastName VARCHAR(50),
  @Email VARCHAR(100),
  @DeptID INT
AS
BEGIN
  INSERT INTO Student (FirstName, LastName, Email, DeptID)
  VALUES (@FirstName, @LastName, @Email, @DeptID);
END;

Call it by passing values for each parameter:

EXEC AddStudent
  @FirstName = 'Abena',
  @LastName = 'Owusu',
  @Email = 'abena.owusu@ucc.edu.gh',
  @DeptID = 3;

Verify the insertion worked:

SELECT *
FROM Student
WHERE LastName = 'Owusu';

Output Parameters — Returning Values

Sometimes you need a procedure to calculate something and return a value back to the caller. Use the OUTPUT keyword to designate a parameter as a return channel.

This procedure counts students in a department and returns the count through an output parameter:

CREATE PROCEDURE CountStudentsInDept
  @DeptID INT,
  @StudentCount INT OUTPUT
AS
BEGIN
  SELECT @StudentCount = COUNT(*)
  FROM Student
  WHERE DeptID = @DeptID;
END;

To call it, declare a variable to receive the output, pass it with the OUTPUT keyword, then read its value:

DECLARE @Count INT;
EXEC CountStudentsInDept
  @DeptID = 1,
  @StudentCount = @Count OUTPUT;
SELECT @Count AS StudentsInDept1;

Modifying and Removing Procedures

To change an existing procedure's logic, use ALTER PROCEDURE. This preserves any permissions already granted on the procedure:

ALTER PROCEDURE GetStudentsByDept
  @DeptID INT
AS
BEGIN
  SELECT
    StudentID,
    FirstName,
    LastName,
    Email,
    GPA
  FROM Student
  WHERE DeptID = @DeptID
  ORDER BY GPA DESC;
END;

To permanently remove a procedure:

DROP PROCEDURE GetStudentsByDept;

How to Use the Stored Procedure Lab

The lab asks you to write a procedure called TransferXP that deducts an amount from one operator's XP and adds it to another's. The left panel shows you a simulation sandbox where you can set the sender ID, receiver ID, and transfer amount. The right panel is the SQL editor.

Here is the same pattern applied to a familiar scenario — transferring GPA points between students (for conceptual illustration):

CREATE OR REPLACE PROCEDURE TransferGPA(
  sender_id UUID,
  receiver_id UUID,
  amount DECIMAL
)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE Student
    SET GPA = GPA - amount
    WHERE StudentID = sender_id;

  UPDATE Student
    SET GPA = GPA + amount
    WHERE StudentID = receiver_id;

  COMMIT;
END;
$$;

Apply this exact structure to the lab. The procedure name must be TransferXP. The first UPDATE must subtract the amount from the sender. The second UPDATE must add the amount to the receiver. The COMMIT must be present to finalise both changes as one atomic unit. The lab validates all four requirements independently and shows you exactly which ones pass and which need correction.