SQL Stored Procedures

In this tutorial, we'll learn about the stored procedures in SQL and how to use them with examples.

In SQL, stored procedure is a set of statement(s) that perform some defined actions. We make stored procedures so that we can reuse statements that are used frequently.

Stored procedures are similar to functions in programming. They can accept parameters, and perform operations when we call them.


Creating a Procedure

We create stored procedures using the CREATE PROCEDURE command followed by SQL commands. For example,

SQL Server

CREATE PROCEDURE us_customers AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';

PostgreSQL

CREATE PROCEDURE us_customers ()
LANGUAGE SQL
AS $$
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
$$;

MySQL

DELIMITER //
CREATE PROCEDURE us_customers ()
BEGIN
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
END //
DELIMITER ;

Oracle

CREATE PROCEDURE us_customers
AS res SYS_REFCURSOR;  
BEGIN
open res for
SELECT customer_id, first_name
FROM Customers
WHERE country = 'USA';
DBMS_SQL.RETURN_RESULT(res);
END;

Executing Stored Procedure

Now whenever we want to fetch all customers who live in the USA, we can simply call the procedure mentioned above. For example,

SQL Server, Oracle

EXEC us_customers;

PostgreSQL, MySQL

CALL us_customers();

Parameterized Procedure

We can pass our own data to stored procedures so the same SQL command works differently for different data.

Suppose we want to fetch records where the value is USA in the country column. So we'll write our SQL statement as,

SELECT *
FROM Customers
WHERE country = 'USA';

And again if we want to fetch records where the value is UK in the country column, we'll write out SQL statement as,

SELECT *
FROM Customers
WHERE country = 'UK';

Notice that in the above two examples, everything is the same except the value to lookup in the country column.

So instead of writing the same code again, we can create a stored procedure and call it with different values. For example,

SQL Server

CREATE PROCEDURE ctr_customers @ctr VARCHAR(50) AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = @ctr;

PostgreSQL

CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))
LANGUAGE SQL
AS $$
SELECT customer_id, first_name
FROM Customers
WHERE Country = ctr;
$$;

MySQL

DELIMITER //
CREATE PROCEDURE ctr_customers (ctr VARCHAR(50))
BEGIN
SELECT customer_id, first_name
FROM Customers
WHERE Country = ctr;
END //
DELIMITER ;

Here, ctr is the parameter which we need to pass while calling the stored procedure. For example,

SQL Server

-- Calling the stored procedure with 'USA' as parameter value
EXEC ctr_customers 'USA';

-- Calling the same stored procedure again with another parameter value 'UK'
EXEC ctr_customers 'UK';

PostgreSQL, MySQL

-- Calling the stored procedure with 'USA' as parameter value
CALL ctr_customers ('USA');

-- Calling the same stored procedure again with another parameter value 'UK'
CALL ctr_customers ('UK');

Multiple Parameterized Procedures

A stored procedure can also take multiple parameters. For example,

SQL Server

-- Creating the stored procedure with cus_id and max_amount as parameters

CREATE PROCEDURE order_details @cus_id INT, @max_amount INT AS
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id
where Customers.customer_id = @cus_id AND Orders.amount < @max_amount;

PostgreSQL

-- Creating the stored procedure with cus_id and max_amount as parameters

CREATE PROCEDURE order_details (cus_id INT, max_amount INT)
LANGUAGE SQL
AS $$
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id
where Customers.customer_id = cus_id AND Orders.amount < max_amount;
$$;

MySQL

-- Creating the stored procedure with cus_id and max_amount as parameters

DELIMITER //
CREATE PROCEDURE order_details (cus_id INT, max_amount INT)
BEGIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id
where Customers.customer_id = cus_id AND Orders.amount < max_amount;
END //
DELIMITER ;

Now to call this function,

SQL Server

EXEC order_details 4, 400;

PostgreSQL

CALL order_details (4, 400);

MySQL

CALL order_details (4, 400);

Here, we've passed two parameters' values in a procedure.


Drop Procedure

We can delete stored procedures by using the DROP PROCEDURE command. For example,

SQL Server, PostgreSQL, MySQL

DROP PROCEDURE order_details;

Here, the SQL command deletes a procedure which is already created.

Did you find this article helpful?