SQL Stored Procedures: The Complete Guide (Oracle, SQL Server, MySQL, PostgreSQL)

Do you want to know what a stored procedure is, how it can help your SQL, and how to create one?

Read this guide to find out all of those, and more.

Table of Contents

We’ll cover a few different topics in this guide:

1. What is an SQL Stored Procedure: the definition and what’s included or required.
2. Benefits of Using Stored Procedures: the reason why you should consider using a stored procedure
3. Disadvantages of Stored Procedures: some points against using these on your database
4. Stored Procedures in Oracle SQL: syntax and examples for creating and running stored procedures in Oracle
5. Stored Procedures in SQL Server: syntax and examples for creating and running stored procedures in SQL Server
6. Stored Procedures in MySQL: syntax and examples for creating and running stored procedures in MySQL
7. Stored Procedures in PostgreSQL: syntax and examples for creating and running stored procedures in PostgreSQL
8. SQL Stored Procedure Best Practices: learn some tips and best practices for working with SQL stored procedures

Click on any of these links to be taken to that place in the document.

 

What is an SQL Stored Procedure?

A stored procedure is a set of SQL statements that can be executed on the database. It is stored as an object in the database.

A stored procedure allows for code that is run many times to be saved on the database and run at a later time, making it easier for yourself and other developers in the future.

It also allows for extra features that aren’t available in regular SQL language to be used, such as loops, output, and manipulation variables.

What’s included in a stored procedure?

A stored procedure includes four things:

• A name. A stored procedure has a name so it can be saved on the database. A stored procedure is also executed using its name.
• Inputs. Stored procedures can have inputs. These are values that you provide to the stored procedure when it is called or executed.
• Body. The logic and code of the stored procedure goes in the body. This is where you define what the stored procedure does.
• Outputs. Stored procedures can return a value, also known as an output. This can be used by another stored procedure or code, output in your IDE, or ignored.

What’s the difference between a stored procedure and a function?

So, right now, you might be thinking, “Hey, that sounds just like a function!”

And you’d be right. It is just like a function.

But, there are some differences.

Functions can be used in any SQL command. Think of the COUNT function, or a function that converts to upper case like UPPER. It can be used in SELECT, INSERT, UPDATE, and DELETE statements, in many places.

However, stored procedures cannot be used in this way. They can only be used by using a specific command such as CALL or EXECUTE (more on that later).

What databases allow stored procedures?

There are several different databases that implement stored procedures, and each of them implements them in its own way. These are offered in languages that extend the functionality of the standard SQL language.

The databases are:

Database	Language
Oracle	PL/SQL (Procedural Language/Structured Query Language)
SQL Server	T-SQL (Transact-SQL)
MySQL	Similar to the SQL/PSM standard
PostgreSQL	PL/pgSQL
DB2	SQL PL

In this guide, I’ll explain how to create and execute stored procedures in several of these different databases.

You can find out more here:

Oracle: https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm

SQL Server: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-2017

MySQL: https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html

PostgreSQL: https://www.postgresql.org/docs/8.0/static/plpgsql.html

 

 

Benefits of Using Stored Procedures

The alternative to using stored procedures is to have the code you need to run stored in your application, or in SQL scripts.

There are many benefits of using stored procedures on your databases.

Security

Stored procedures offer another level of security on your database.

WIthout stored procedures, applications would be able to run SQL commands on the database directly, such as INSERT. This means the application user will need read and write access to the tables, knowledge on how to insert the data in the correct way, as well as extra security to protect from SQL injection.

If you use a stored procedure, you can include your SQL code inside the procedure, and then let applications access the stored procedure. Depending on the database, they won’t need write access to the underlying table – they only need to be able to run the stored procedure. There is also less risk of SQL injection as being inside a stored procedure can handle that (you can write your own SQL injection-proof code).

Modular

Stored procedures mean that the code required to manipulate the database is stored in a database object, and not inside the application.

This means that it can be developed separately, which is a big advantage of object-oriented programming, and is useful when working with teams as one developer can work on the application code and another can work on the stored procedure.

It can also be easier to maintain. If you know that there is an issue with the data from the database, you can likely isolate it to the stored procedure.

Easier to tune

Because all of the code exists in the stored procedure inside the database, it can be easier for the developer or database administrator to tune or improve the performance.

Stored procedures can have their code analysed using database-specific tools, logs can be analysed, and execution plans created to see how code is run. This is easier to do in the database in a stored procedure than inside application code.

Reduce network traffic

If a query is stored in the application code, the query is sent to the server. The server then looks up the results, and sends the results back to the application for further processing.

This could be hundreds or thousands of records. It might not seem like much, but if it’s something that’s done a lot of times, this can really add up and the extra traffic could slow the process down.

Using a stored procedure means the code to call the stored procedure is sent from the application to the database. The database runs the stored procedure, processes the results, and sends those results back to the application.

All of the processing is done on the database, and the final and hopefully smaller result set is sent to the application. This often means less network traffic, and ultimately a faster process.

Business Logic is in One Place

Databases can store data for many different applications. Often they are designed for just a single application, but many applications can connect to them.

For the business logic to be consistent across these applications, it’s better to store this logic in the database inside a stored procedure. For example, the logic to calculate the payment due date of an order, or the number of business days between two dates.

This means that any application that uses this database can use these stored procedures and get the same results.

It also means the code is reusable. Other applications can use this same code and not have to write it themselves.

The code is easier to maintain as it is in a single place, not spread out over multiple applications.

 

Disadvantages of Using Stored Procedures

There are some disadvantages to using stored procedures, however.

Debugging In Some IDEs

Starting a debugging session or process for application code can be easy. Most IDEs, such as Visual Studio or Eclipse, include a debugging feature.

However, many SQL IDEs don’t have a solid debugging feature for stored procedures. The popular ones, such as Oracle SQL Developer and Toad, have this feature built in. But if you’re using another IDE, it may not have a debugging feature.

This means it’s harder to find problems in your stored procedure. If you’re using an IDE with a good debugging tool, then it’s OK, but that’s not always the case.

Version Control

I think the biggest disadvantage to stored procedures is being able to include it in version control systems.

Application code can be easily included in your version control system as it would sit inside your project.

However, with a stored procedure, the definition is on the database. To store a version of the stored procedure, you would need to use a .sql file with the CREATE statement stored inside it, and include that in your version control.

You would need to remember to keep that file up to date. It’s not difficult, but it’s another manual step in the process, and any time there is a manual step, there is room for error.

Hard to Move Between Databases

Another disadvantage of using stored procedures is that it is harder to move the business logic between databases.

If you want to move your database from one vendor (e.g. Oracle, SQL Server) to another, it’s easier to do this if your queries are in the application code. If they are in stored procedures, you’ll need to translate them to the new vendor’s syntax and recreate them.

This is another step you’ll have to take during your migration.

However, it’s not very often that companies move their applications from one vendor to another. If they do, there’s a range of steps that would need to be performed, including making sure that all of the SQL is valid and updated.

Here are some other thoughts on the advantages and disadvantages of stored procedures:

SegueTech: https://www.seguetech.com/advantages-and-drawbacks-of-using-stored-procedures-for-processing-data/

StackOverflow: https://stackoverflow.com/questions/226859/disadvantage-of-stored-procedures

Toad World: https://community.toadworld.com/platforms/oracle/w/wiki/5809.stored-procedure-and-functions-advantages

Stored Procedures in Oracle SQL

Create a Stored Procedure in Oracle SQL

To create a stored procedure, we use the CREATE PROCEDURE command in Oracle SQL.

The syntax is:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter list)]
IS
declaration_code
BEGIN
execution_code
EXCEPTION
exception_code
END;

Let’s take a look at this syntax.

• CREATE PROCEDURE: this indicates we are creating a new stored procedure.
• [OR REPLACE]: this is in square brackets as it is optional. It means that the existing stored procedure will be replaced with the procedure mentioned here. You don’t have to drop and create the stored procedure in two different statements.
• procedure_name: this is the name of the new stored procedure.
• (parameter_list): this is optional, but it is where you specify the parameters for a stored procedure. These would be input or output parameters.
• declaration_code: this is where any variables that are used within the stored procedure are declared.
• BEGIN: this identifies the start of the section of code that is run when the stored procedure is called.
• execution_code: this is the code that is run. This is where the majority of your code is: queries, variable assignments, IF statements, and so on.
• EXCEPTION: this indicates the start of the exception code, which is the code that runs if an exception/error is found while running the stored procedure.
• exception_code: this is the code that is run if an exception is found.
• END: The end of the stored procedure.

Now let’s see an example of a stored procedure:

CREATE PROCEDURE getCurrentDate
IS
datevalue DATE;
BEGIN
  SELECT SYSDATE
  INTO datevalue
  FROM dual;
END;

This is a simple stored procedure that gets the current date using the SYSDATE function.

Note: this procedure does not return the datevalue or SYSDATE. It simply selects it and stores it into the datevalue variable. You could use an OUT parameter to return it, or display it on the screen using dbms_output.put_line().

Execute a Stored Procedure in Oracle SQL

To run a stored procedure, you can either:

• Use the EXECUTE command
• Call the stored procedure name from within another stored procedure.

For example, let’s say you had a procedure called getDate.

You could call it in this way:

EXECUTE getDate;

Or, use EXEC for short:

EXEC getDate;

Or, if you want to run it inside another stored procedure, just use the name:

getDate;

Simple Example

This is a simple example of a stored procedure. It will lookup data in an employee table and return the results.

CREATE PROCEDURE Employee_ShowAll
IS
BEGIN
  SELECT first_name, last_name, date_of_birth, address
  FROM employee;
END;

Example with Parameters

Here’s an example of a stored procedure that uses parameters.

CREATE PROCEDURE GetEmployeeLastName (emp_id IN NUMBER, emp_last_name OUT VARCHAR2(200))
IS
BEGIN
  SELECT last_name
  INTO emp_last_name
  FROM employee
  WHERE id = emp_id;
END;

 

 

Stored Procedures in SQL Server

Create a Stored Procedure in SQL Server

To create a stored procedure on SQL Server, use the following syntax:

CREATE [OR ALTER] { PROC | PROCEDURE } procedure_name [parameter_list]
AS
BEGIN
  execution_section
END;

Let’s take a look at this syntax:

• CREATE: the way to create a stored procedure is to start with the CREATE keyword.
• [OR ALTER]: this is an optional keyword, and it lets you alter an existing stored procedure without having to drop it and recreate it in two separate steps.
• PROC|PROCEDURE: to create a stored procedure, you can either specify the full word PROCEDURE, or use PROC for short. I prefer the full word, but the abbreviated form is available.
• procedure_name: this is where you can give your procedure a name.
• [parameter_list]: this is where you specify the parameters for your procedure, both the inputs and outputs.
• BEGIN: this defines the start of the execution section or the code that is run when the procedure is called.
• execution_section: this is the code that is run.
• END: this indicates the end of the stored procedure.

An example of a stored procedure in SQL Server is:

CREATE PROCEDURE getCurrentDate
AS
SELECT GETDATE;

This procedure, like the example for Oracle, will return the current date.

Execute a Stored Procedure in SQL Server

To run a stored procedure in SQL Server, you can:

• Run the EXECUTE or EXEC statement
• Call the procedure from another stored procedure

If we use our earlier example of getDate, we can call it by:

EXECUTE getDate;

Or, using the shorthand version:

EXEC getDate;

You can use the same command inside another stored procedure.

Simple Example

This is a simple example of a stored procedure. It will lookup data in an employee table and return the results.

CREATE PROCEDURE Employee_ShowAll
AS
BEGIN
  SET NOCOUNT ON;
  SELECT first_name, last_name, date_of_birth, address
  FROM employee;
END;
GO

Example with Parameters

Here’s an example of a stored procedure that uses parameters.

CREATE PROCEDURE GetEmployeeLastName (@emp_id INT, @emp_last_name VARCHAR(200) OUT)
AS
BEGIN
  SELECT @emp_last_name = last_name
  FROM employee
  WHERE id = @emp_id;
END;

 

Stored Procedures in MySQL

Create a Stored Procedure in MySQL

To create a stored procedure in MySQL, use the following syntax:

DELIMITER delimiter_character
CREATE PROCEDURE procedure_name (parameters)
procedure_body delimiter_character
DELIMITER ;

This syntax includes a few things:

• A DELIMITER statement, which allows for stored procedures to be created. See below for a more detailed explanation.
• CREATE PROCEDURE: this starts the procedure creation process.
• procedure_name:the name of the procedure.
• parameters: the parameters to be passed in and out of the stored procedure.
• procedure_body: This is the code that your procedure will run, and is the largest part of the procedure.
• delimiter_character: use the delimiter character defined above to end the stored procedure.
• DELIMITER ; is used to reset the character back to a semicolon.

An example of a stored procedure in MySQL is:

DELIMITER $
CREATE PROCEDURE getCurrentDate
BEGIN
  SELECT NOW();
END$
DELIMITER ;

This looks similar to previous examples, where we select the current date from the database.

However, there is one difference: the delimiter command.

What is the DELIMITER Command in MySQL Stored Procedures?

So you might have seen the DELIMITER command in MySQL stored procedure examples online, in your own scripts, or in the example above.

What is this DELIMITER command?

And why is it needed?

By default, MySQL treats the default delimiter, the semicolon ; as an end of statement command. When this character is found, the statement ends.

In other databases, the database engine knows that a CREATE PROCEDURE ends when the END statement is found.

However, MySQL stored procedures are still quite new, and this logic hasn’t been added yet.

So, if we write a stored procedure, and it includes a semicolon, MySQL sees that as the end of the stored procedure.

But the SQL needs the semicolon!

This is where the DELIMITER comes in.

The DELIMITER command in MySQL lets you set a delimiter, or statement separator character, so you can create a stored procedure successfully. It’s used before the CREATE, and reset after the procedure is created. It’s often set to a rarely used character such as $$ or //.

 

Execute a Stored Procedure in MySQL

To execute a stored procedure in MySQL, you have to use the CALL command.

CALL getCurrentDate();

This will run the procedure and can be used inside stored procedures.

Simple Example

This is a simple example of a stored procedure. It will lookup data in an employee table and return the results.

DELIMITER $
CREATE PROCEDURE Employee_ShowAll
BEGIN
  SELECT first_name, last_name, date_of_birth, address
  FROM employee;
END$
DELIMITER ;

Example with Parameters

Here’s an example of a stored procedure that uses parameters.

DELIMITER $
CREATE PROCEDURE GetEmployeeLastName (IN emp_id INT, OUT emp_last_name VARCHAR(200))
BEGIN
  SELECT last_name
  INTO emp_last_name
  FROM employee
  WHERE id = emp_id;
END$
DELIMITER ;

 

 

Stored Procedures in PostgreSQL

Create a Stored Procedure in PostgreSQL

To create a stored procedure in PostgreSQL, use the following syntax:

CREATE OR REPLACE FUNCTION procedure_name (parameters)
RETURNS type AS $
BEGIN
  procedure_body
END
$ LANGUAGE plpgsql;

This syntax includes a few things:

• CREATE OR REPLACE FUNCTION: This is how the stored procedure starts. In PostgreSQL, both functions and stored procedures use the CREATE FUNCTION command.
• procedure_name: the name of the procedure.
• parameters: the parameters to be passed in and out of the stored procedure.
• RETURN type: the data type to be returned
• AS $$: defines the start of the procedure body
• procedure_body: This is the code that your procedure will run, and is the largest part of the procedure.
• END $$ LANGUAGE plpgsql: this is the end of the stored procedure. The language is defined here, as PostgreSQL can support stored procedures in different languages.

An example of a stored procedure in PostgreSQL is:

CREATE OR REPLACE FUNCTION getCurrentDate()
RETURNS void AS $
BEGIN
  SELECT NOW()
END
$ LANGUAGE plpgsql;

This procedure does the same thing as earlier examples, by finding the current date.

Execute a Stored Procedure in PostgreSQL

To execute a stored procedure in PostgreSQL, we can use the SELECT statement or the PERFORM statement.

SELECT getCurrentDate();

Or:

PERFORM getCurrentDate();

The PERFORM command is useful if you want to use this inside another stored procedure.

Simple Example

This is a simple example of a stored procedure. It will lookup data in an employee table and return the results.

CREATE OR REPLACE FUNCTION Employee_ShowAll
RETURNS void AS $
BEGIN
  SELECT first_name, last_name, date_of_birth, address
  FROM employee;
END
$ LANGUAGE plpgsql;

Example with Parameters

Here’s an example of a stored procedure that uses parameters.

CREATE OR REPLACE FUNCTION GetEmployeeLastName (emp_id INT)
RETURNS VARCHAR AS $
DECLARE
  emp_last_name VARCHAR(200);
BEGIN
  SELECT last_name
  INTO emp_last_name
  FROM employee
  WHERE id = emp_id;
  RETURN emp_last_name;
END
$ LANGUAGE plpgsql;

SQL Stored Procedure Best Practices

So we’ve looked at what stored procedures are, how to create them, and some examples.

How can we make sure we’re doing the right thing when we create them?

There is a range of best practices or recommendations that I can suggest here. These are things I’ve learned myself – usually from doing it one way and being told a better way to do it!

Let’s take a look.

Don’t Use Data Types as Prefixes

I’ve often seen SQL code, either in live systems or online examples, that include the type of object in the object’s name.

For example, a stored procedure called sp_updateCustomer. Or a table called tbl_customer. Or a view called vw_top_customers.

There is no need to add the object type as a prefix to the name of the object. It’s rare that you and others won’t know what type of object you’re working with when you enter the name. With autocomplete in many IDEs it’s easy to only select table names when appropriate: typing in SELECT and FROM will often show a list of tables and views in the database, not stored procedures.

It makes it harder for any future changes, because if you decide to use a synonym, then the name of the synonym indicates it is not a synonym.

It also reduces the number of characters you can use. In Oracle, the limit is around 30 characters, so taking up 3 or 4 characters with a prefix is not ideal.

Specify the Schema

When creating a stored procedure, it’s good practice to specify the schema that the stored procedure will be stored in.

Even if it is just the “dbo” schema in SQL Server or your current schema, specifying it makes it clear who the object is supposed to belong to.

Use Table Aliases In Queries

When writing your queries inside the stored procedure, it’s a good idea to use table aliases.

Take a look at this query, without table aliases:

SELECT
employee.id,
employee.first_name,
employee.last_name,
employee.salary,
department.id,
department.name,
location.id,
location.name
FROM employee
INNER JOIN department ON employee.dept_id = department.id
INNER JOIN location ON employee.location_id = location.id
WHERE employee.salary > 20000
ORDER BY employee.last_name, employee.first_name;

Now, take a look at this query, that uses table aliases:

SELECT
e.id,
e.first_name,
e.last_name,
e.salary,
d.id,
d.name,
l.id,
l.name
FROM employee e
INNER JOIN department d ON e.dept_id = d.id
INNER JOIN location l ON e.location_id = l.id
WHERE e.salary > 20000
ORDER BY e.last_name, e.first_name;

Which one is easier to read?

Using table aliases is also easier to write, as you can just enter the alias each time you want to specify a column. Many IDEs will trigger the auto-complete once you do this, making writing the query even easier.

Use Column Aliases for Expressions

When using functions or expressions in your queries, the output from those can be messy. The column headings are just a concatenation of the columns and functions used.

However, using column aliases can help in a few ways:

• It cleans up your output, making it easier to view.
• It makes it easier to refer to columns outside the stored procedure, e.g. in other stored procedures or in the application.
• It’s easier to modify the query if subqueries are needed.
• It can help identify multiple columns that have the same names.

Here’s an example of using a column alias:

SELECT
e.id AS emp_id,
e.first_name,
e.last_name,
e.salary + e.bonus AS total_salary
FROM employee e;

As you can see, there are two column aliases here.

The first shows the e.id column as emp_id, because without this label, it would just show as id. If we were to add in another table, it wouldn’t be clear what id this relates to.

The second is naming the salary + bonus columns as total_salary. This makes it clearer because of the reasons above.

Upper Case Keywords

Another general tip for writing SQL that also applies to stored procedures is to capitalise your SQL keywords.

SQL is not a case sensitive language. However, it’s a good idea to write your SQL keywords in upper case, as it makes it easier to read (in my opinion).

So, instead of this:

select first_name, last_name
from employee
where salary > 20000;

We should write this:

SELECT first_name, last_name
FROM employee
WHERE salary > 20000;

This should be the case whether you’re writing production code, quick scripts, or even code for your own blog or website. I’ve seen so many articles that show demos of SQL code that use lowercase keywords, and without formatting, it looks messy and hard to read.

Use Meaningful Variable Names

When using stored procedures, you have the ability to use variables.

You should use meaningful names for these variables when creating them.

This is the case for programming in general, but you should be able to tell what the variable represents by looking at its name.

So, rather than variables with these names:

• x
• chrName
• foo

Try use variable names that make sense:

• first_name
• employee_count
• region_total_salary

Then you can tell what the variable represents by looking at the name.

Avoid Abbreviated Keywords

Several vendors (such as SQL Server) offer the ability to create a stored procedure by using the CREATE PROC command, rather than the full CREATE PROCEDURE command.

While I recognise that this is easier to type, it’s only a few extra characters to type the whole word. The keywords CREATE PROCEDURE make it clear that you’re creating a procedure, and it makes sense to both experienced developers who know about the shorthand, and to new developers.

Use Spaces Often

Adding spaces to your code improves the readability. This is both for spaces in between words, and line breaks.

I put spaces after and sometimes before symbols in my SQL queries. I add spaces either side of = symbols and similar symbols. I add spaces after commas when specifying groups and orders.

This all makes the query easier to read.

For example, this is part of a query without spacing:

WHERE e.salary>20000;

And this is the same code with spacing:

WHERE e.salary > 20000;

It might not seem like much, but adding spaces in your code in a consistent manner, and where it makes sense, will improve the readability of it, and help other developers.

Add Output Parameters at the End

When defining parameters for a stored procedure, there’s no specific order that you need to put the parameters in. It will still run, as long as the code that calls the procedure will specify the values in the same order.

You have the ability to specifu output parameters in stored procedures. These are parameters that return a value back to the calling code.

I think it’s a good habit to define these parameters at the end of the parameter list.

For example:

CREATE PROCEDURE updateEmployeeSalary (
salary IN NUMBER, bonus IN NUMBER, emp_id IN NUMBER, payment_tier OUT NUMBER
)...

This procedure has a few parameters, and the output parameter (the payment_tier) is specified last. This makes it easier to see that this parameter is an output parameter.

Format Your Code

I’ve mentioned a few tips in this section that try to make your code look neater.

Overall, though, your code should be formatted.

This means the code is laid out in an easy to read and consistent manner.

Many IDEs have a code formatting feature, allowing you to press a button and automatically format the code. Many of these IDEs even allow you to configure how the formatting is done (for example, the commas in the SELECT statement could go at the start or the end of each line).

I recommend formatting your stored procedure code. Like everything else in this section, it makes it easier to maintain and easier to read.

Use SET NOCOUNT ON in SQL Server

SQL Server has a statement called SET NOCOUNT ON.

Adding this as the first line in your procedure, just after the AS keyword, will turn off messages sent back from the server for each SELECT, INSERT, UPDATE, DELETE, or MERGE statement run.

Doing this will improve the performance of your stored procedure.

Use Packages in Oracle

Steve Feuerstien wrote a great article mentioning that all PL/SQL code (for Oracle database) should be stored in packages.

You can read his article for more information, but in short, creating packages to house your stored procedures is a good idea as it means:

• You can group together related functionality
• You can make better use of caches
• You can improve maintenance by hiding some information from the users and ensuring it is in a single location

 

Conclusion

Stored procedures are a great feature of a database. They have many advantages and handle much more than standard SQL code.

What has your experience with stored procedures been? Is it all new to you, or have you written some before? Do you have any questions on stored procedures? Let me know in the comments below.