{"id":272,"date":"2024-08-23T02:28:16","date_gmt":"2024-08-23T02:28:16","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=272"},"modified":"2025-05-16T06:27:06","modified_gmt":"2025-05-16T06:27:06","slug":"sql-stored-procedures","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/sql-stored-procedures\/","title":{"rendered":"SQL Stored Procedures"},"content":{"rendered":"\n

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\u2019s especially helpful when you\u2019re dealing with repeated tasks or logic that you want to manage in one place.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL Stored Procedures Syntax<\/h2>\n\n\n\n

For MySQL \/ PostfgreSQL:<\/strong><\/p>\n\n\n\n

CREATE PROCEDURE procedure_name (parameters)\nBEGIN\n  -- your SQL statements\nEND;<\/pre><\/div>\n\n\n\n
To use it:<\/p>\n\n\n\n
CALL procedure_name(arguments);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
For SQL Server:<\/strong><\/p>\n\n\n\n
CREATE PROCEDURE procedure_name\n  @param1 datatype,\n  @param2 datatype\nAS\nBEGIN\n  -- your SQL statements\nEND;<\/pre><\/div>\n\n\n\n
To run:<\/p>\n\n\n\n
EXEC procedure_name arguments;<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Stored Procedures Example<\/h2>\n\n\n\n
This query creates a procedure called GetAllDoctors<\/code> that returns all records from the Doctors<\/code> table.<\/p>\n\n\n\n
CREATE PROCEDURE GetAllDoctors()\nBEGIN\n  SELECT * FROM Doctors;\nEND;<\/pre><\/div>\n\n\n\n
To call it:<\/p>\n\n\n\n
CALL GetAllDoctors();<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Stored Procedures With One Parameter Example<\/h3>\n\n\n\n
This query defines a procedure that accepts a single parameter and returns appointments for the doctor with the specified ID.<\/p>\n\n\n\n
CREATE PROCEDURE GetAppointmentsByDoctor(IN doc_id INT)\nBEGIN\n  SELECT * FROM Appointments\n  WHERE doctor_id = doc_id;\nEND;<\/pre><\/div>\n\n\n\n
To call it:<\/p>\n\n\n\n
CALL GetAppointmentsByDoctor(1);<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Stored Procedures With Multiple Parameters Example<\/h3>\n\n\n\n
This query creates a stored procedure that takes two parameters: a doctor\u2019s ID and a date. It returns only the appointments that match both.<\/p>\n\n\n\n
CREATE PROCEDURE GetDoctorAppointmentsByDate(IN doc_id INT, IN app_date DATE)\nBEGIN\n  SELECT * FROM Appointments\n  WHERE doctor_id = doc_id AND appointment_date = app_date;\nEND;<\/pre><\/div>\n\n\n\n
To call it:<\/p>\n\n\n\n
CALL GetDoctorAppointmentsByDate(2, '2024-09-20');<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Stored Procedures Visual Diagram<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL Stored Procedures Labs<\/h2>\n\n\n\n