SQL Stored Procedures

A stored procedure is basically a set of SQL commands that you save in the database and run whenever you need it. Instead of writing the same queries again and again, you just call the procedure. It’s especially helpful when you’re dealing with repeated tasks or logic that you want to manage in one place.

SQL Stored Procedures Syntax

For MySQL / PostfgreSQL:

CREATE PROCEDURE procedure_name (parameters)
BEGIN
  -- your SQL statements
END;

CREATE PROCEDURE procedure_name (parameters)

BEGIN

  -- your SQL statements

END;

To use it:

CALL procedure_name(arguments);

CALL procedure_name(arguments);

For SQL Server:

CREATE PROCEDURE procedure_name
  @param1 datatype,
  @param2 datatype
AS
BEGIN
  -- your SQL statements
END;

CREATE PROCEDURE procedure_name

  @param1 datatype,

  @param2 datatype

AS

BEGIN

  -- your SQL statements

END;

To run:

EXEC procedure_name arguments;

EXEC procedure_name arguments;

SQL Stored Procedures Example

This query creates a procedure called GetAllDoctors that returns all records from the Doctors table.

CREATE PROCEDURE GetAllDoctors()
BEGIN
  SELECT * FROM Doctors;
END;

CREATE PROCEDURE GetAllDoctors()

BEGIN

  SELECT * FROM Doctors;

END;

To call it:

CALL GetAllDoctors();

CALL GetAllDoctors();

SQL Stored Procedures With One Parameter Example

This query defines a procedure that accepts a single parameter and returns appointments for the doctor with the specified ID.

CREATE PROCEDURE GetAppointmentsByDoctor(IN doc_id INT)
BEGIN
  SELECT * FROM Appointments
  WHERE doctor_id = doc_id;
END;

CREATE PROCEDURE GetAppointmentsByDoctor(IN doc_id INT)

BEGIN

  SELECT * FROM Appointments

  WHERE doctor_id = doc_id;

END;

To call it:

CALL GetAppointmentsByDoctor(1);

CALL GetAppointmentsByDoctor(1);

SQL Stored Procedures With Multiple Parameters Example

This query creates a stored procedure that takes two parameters: a doctor’s ID and a date. It returns only the appointments that match both.

CREATE PROCEDURE GetDoctorAppointmentsByDate(IN doc_id INT, IN app_date DATE)
BEGIN
  SELECT * FROM Appointments
  WHERE doctor_id = doc_id AND appointment_date = app_date;
END;

CREATE PROCEDURE GetDoctorAppointmentsByDate(IN doc_id INT, IN app_date DATE)

BEGIN

  SELECT * FROM Appointments

  WHERE doctor_id = doc_id AND appointment_date = app_date;

END;

To call it:

CALL GetDoctorAppointmentsByDate(2, '2024-09-20');

CALL GetDoctorAppointmentsByDate(2, '2024-09-20');

SQL Stored Procedures Visual Diagram

SQL Stored Procedures Labs