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
STATEMENTS
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 you run this procedure and input a state code, it will return all records from table techcrunch 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 proc_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 a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server... faster and easier.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.
