CREATE PROCEDURE syntax in SQL

Introduction

A procedure is a saved set of instructions in the database that you can run anytime to do a task automatically.

You want to repeat a task like adding a new user without writing the same code again.

You need to organize complex steps into one simple command.

You want to share a common task with others without giving them the full code.

You want to improve speed by running pre-made commands inside the database.

Syntax

SQL

CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)
BEGIN
  -- SQL statements
END;

Parameters are optional and let you send values to the procedure when you run it.

The BEGIN and END keywords group the SQL statements inside the procedure.

Examples

A simple procedure with no parameters that shows a greeting message.

SQL

CREATE PROCEDURE greet()
BEGIN
  SELECT 'Hello, world!';
END;

A procedure that takes two numbers and returns their sum.

SQL

CREATE PROCEDURE add_numbers(IN num1 INT, IN num2 INT)
BEGIN
  SELECT num1 + num2 AS sum;
END;

A procedure that returns all rows from the users table.

SQL

CREATE PROCEDURE get_users()
BEGIN
  SELECT * FROM users;
END;

Sample Program

This procedure shows today's date when called.

SQL

CREATE PROCEDURE show_date()
BEGIN
  SELECT CURRENT_DATE() AS today;
END;

CALL show_date();

Important Notes

Use CALL procedure_name() to run the procedure after creating it.

Procedures help keep your database tasks organized and reusable.

Not all SQL databases use the exact same syntax; check your database's documentation.

Summary

CREATE PROCEDURE saves a set of SQL commands to run later.

You can add parameters to send information into the procedure.

Use CALL to run the procedure and get results.