CREATE PROCEDURE

Create a procedure.

With CREATE PROCEDURE you name and store SQL code. Later run with CALL.

CREATE PROCEDURE add_product(name VARCHAR, price DECIMAL)
LANGUAGE SQL
AS $$
  INSERT INTO products (name, price) VALUES (name, price);
$$;

Procedure that adds a product.

The procedure add_product is created.

CREATE PROCEDURE — details

A procedure can take parameters. They are defined in parentheses.

Each parameter has a name and a type (e.g. name VARCHAR, price DECIMAL); when calling, values are given in the same order.

Create the add_product procedure with name and price parameters.

Show example solution

CREATE PROCEDURE add_product(name VARCHAR, price DECIMAL)
LANGUAGE SQL
AS $$
  INSERT INTO products (name, price) VALUES (name, price);
$$;

Add AS

CREATE PROCEDURE add_product(name VARCHAR, price DECIMAL)
LANGUAGE SQL
____ $$
  INSERT INTO products (name, price) VALUES (name, price);
$$;

Show answer

CREATE PROCEDURE add_product(name VARCHAR, price DECIMAL)
LANGUAGE SQL
AS $$
  INSERT INTO products (name, price) VALUES (name, price);
$$;

Next lesson: Parameters