How to create and execute Stored Procedure in SQL, with example

I’m going to show some simple examples of creating and executing stored procedures in some common databases that support stored procedures: MySQL, SQL Server, PostgreSQL.

Creating and executing a stored procedure in MySQL:

The basic syntax for creating a stored procedure:

CREATE PROCEDURE proc_name(inout params)
BEGIN
DECLARE variables
DECLARE CURSORS
DECLARE handlers
STATMENTS
END;

For example, I will create a procedure test_proc:

CREATE PROCEDURE test_proc (IN addr VARCHAR(20))
BEGIN
  SELECT * FROM techcrunch
  WHERE `state` = addr;
END;

When run this procedure and input a state code, it will return all records from table tech crunch with the state value that match the input.

To call this procedure with state code “AZ”

CALL test_proc ("AZ");

Creating and executing a stored procedure in SQL Server:

Basic syntax to create stored procedure:

CREATE PROCEDURE proce_name @params type
AS
STATEMENTS
GO;

Go back to the example above with MySQL:

CREATE PROCEDURE test_proc @addr VARCHAR(20)
AS
BEGIN
  SELECT * FROM techcrunch
  WHERE state = @addr;
END;

To call the test_proc created just now:

EXEC test_proc CA;

Creating and executing a stored procedure in PostgreSQL:

From PostgreSQL 11, you can create a procedure:

CREATE PROCEDURE test_proc(addr VARCHAR(20))
LANGUAGE SQL
AS $$
  SELECT * FROM techcrunch WHERE "state" = addr;
$$;

and call it:

CALL test_proc('CA');

But for the earlier versions of PostgreSQL, stored procedures are not supported.

SQLite also doesn’t support stored procedure.

Need a good GUI Tool for relational database? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server… faster and easier. 