How to Create a Stored Procedure in SQL

If you need to package up blocks of code to run regularly, you may want to create a stored procedure in SQL. In this article, we will cover the basics of creating stored procedures. You will also see a SQL stored procedure example (or three or four).

What is a SQL Stored Procedure?

A stored procedure is a set of one or more SQL statements that can be stored in a database and executed later. This is helpful when you need to perform a complex set of operations that are frequently executed. They can accept input parameters and return multiple output parameters. You use stored procedures to perform tasks that would be difficult or inefficient using regular SQL statements. You can call a stored procedure from within other SQL statements, or from application code.

There are several reasons why you might want to use stored procedures.

1. Stored procedures improve database performance. You can call a stored procedure and execute a single request that will perform multiple actions. This reduces the number of commands sent to the database server. This can reduce network traffic and improve the overall speed of the database application.
2. They provide a layer of security and abstraction between the database and the client application. Encapsulating complex database operations within a stored procedure allows you to hide the underlying implementation details from the client application. This can help to prevent SQL injection attacks and other security vulnerabilities.
3. Stored procedures help to maintain the integrity of the data in the database. Stored procedures ensure that the operations are performed consistently and correctly, and that the data remains in a valid state.
4. Stored procedures make it easier to develop and maintain database applications. By centralizing complex database operations, you can reduce the amount of SQL code that needs to be written and maintained in the client application. This makes it easier to change the underlying database operations without impacting the client application.
5. Stored procedures can be used to encapsulate business logic and rules within the database. This helps to ensure that the logic is consistently applied across different applications and users.

Capabilities of Stored Procedures

Stored procedures give you a lot of power and flexibility. You can execute virtually any SQL statement from within a procedure.

One of the main uses of stored procedures is performing data manipulation. Updating, inserting, and deleting records are common tasks for stored procedures. For example, you can create a stored procedure that accepts input parameters and then uses those parameters to update a customer record in the database. That way anyone who interacts with the stored procedure is doing so in the same way.

You can also return values from a stored procedure. Stored procedures can return scalar values. A scalar value is a single data value – like a number, a string, or a date. Additionally, a SQL stored procedure can return a table value. Table values are result sets that are comprised of rows and columns.

Creating a Stored Procedure in Microsoft SQL Server

If you need to create a stored procedure in MS SQL Server, you can use the CREATE PROCEDURE command and provide the required information. Here is an example of the syntax:

CREATE PROCEDURE procedure_name
    @parameter_name datatype [= default_value]
AS
BEGIN
    -- stored procedure body
    -- (SQL statements, control-of-flow statements, etc.)
END

Here is an example of a stored procedure that accepts a customer ID and a new customer name as input parameters, and updates the customer record with the new name:

CREATE PROCEDURE UpdateCustomerName
    @CustomerID int,
    @Name nvarchar(50)
AS
BEGIN
    UPDATE Customers
    SET Name = @Name
    WHERE CustomerID = @CustomerID
END

Once you have created the stored procedure, you will need to run (or execute) it. You can do that by running the following code.

EXEC UpdateCustomerName @CustomerID = 123, @Name = 'John Smith'

This would update the customer with ID 123 to have a name of ‘John Smith’.

Optional Parameters in a SQL Server Stored Procedure

Sometimes you want the ability to use optional parameters in your stored procedure. For MS SQL Server, you can specify optional parameters using the following syntax:

CREATE PROCEDURE UpdateCustomer
    @CustomerID int,
    @Name nvarchar(50) = NULL,
    @Email nvarchar(50) = NULL
AS
BEGIN
    UPDATE Customers
    SET Name = COALESCE(@Name, Name),
        Email = COALESCE(@Email, Email)
    WHERE CustomerID = @CustomerID
END

This stored procedure accepts three input parameters: @CustomerID, @Name, and @Email. The @Name and @Email parameters are optional, and have default values of NULL. This means that if you execute the stored procedure and omit the @Name or @Email parameters, the stored procedure will not update the corresponding column in the Customers table. You can now use this stored procedure to update either the name or the email address (or neither). It will keep the current value if no value is provided.

Listing MS SQL Stored Procedures

If you need a list of all stored procedures in your MS SQL database, you can query the information schema for that. You can use the information_schema.routines table to retrieve a list. Filter your query on the ROUTINE_SCHEMA field to see only the procedures you’ve created in that schema.

SELECT *
FROM information_schema.routines
WHERE routine_schema = 'dbo'

Creating a MySQL Procedure

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

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- stored procedure body
    -- (SQL statements, control-of-flow statements, etc.)
END

Here is an example of a stored procedure that accepts a customer ID and a new customer name as input parameters, and updates the customer record with the new name:

CREATE PROCEDURE UpdateCustomerName (IN CustomerID INT, IN CustomerName VARCHAR(50))
BEGIN
    UPDATE Customers
    SET Name = COALESCE(CustomerName, Name)
    WHERE ID = CustomerID;
END

This stored procedure can be executed by using the following statement:

CALL UpdateCustomerName(123, 'John Smith');

This would update the customer with ID 123 to have a name of ‘John Smith’. If you were to pass in a NULL parameter for the CustomerName, it would result in no change to the record. You will have to pass in all the parameters that are specified. You will receive an error if you attempt to execute the CALL statement without both parameters.

Get a List of MySQL Stored Procedures

If you want a list of MySQL stored procedures, you can query the information_schema.routines table. You can use the ROUTINE_SCHEMA column to find only those procedures that you have created within your database schema.

SELECT *
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'dev_playbook';

PostgreSQL Stored Procedures

PostgreSQL does not provide stored procedure functionality explicitly until version 12. If you are running version 11 or earlier, you will need to create a function in PostgreSQL. Here is the syntax you would use:

CREATE FUNCTION function_name (parameter_list)
RETURNS return_type
AS
$$
    -- function body
    -- (SQL statements, control-of-flow statements, etc.)
$$
LANGUAGE language;

Here is an example of a stored procedure that accepts a customer ID and a new customer name as input parameters, and updates the customer record with the new name:

CREATE FUNCTION UpdateCustomerName (CustomerID INT, Name VARCHAR(50))
RETURNS VOID
AS
$$
BEGIN
    UPDATE Customers
    SET Name = Name
    WHERE CustomerID = CustomerID;
END
$$
LANGUAGE plpgsql;

To execute the function, you will use the SELECT syntax. Instead of providing a FROM clause, you will simply provide the function with the parameters. Here is an example:

SELECT UpdateCustomerName(123, 'John Smith');

This would update the customer with ID 123 to have a name of ‘John Smith’. As with MySQL, you will need to provide all of the parameters in your function call. You can provide NULL as the value for a parameter if you don’t have a value.

PostgreSQL Version 12 Stored Procedures

Starting with version 12, you can now create a stored procedure in PostgreSQL. The syntax is similar to MySQL syntax in that you will use the CREATE PROCEDURE command. Here is an example:

CREATE PROCEDURE UpdateCustomerName(CustomerID INT, CustomerName VARCHAR(50))
LANGUAGE sql
AS $$
    UPDATE sales_records SET
        customer_name = coalesce(CustomerName, Name)
    WHERE id = CustomerID
$$;

You would then call this stored procedure using the CALL command. You must provide all of the defined parameters, but you can use a NULL value if necessary.

PostgreSQL Stored Procedure Meta Data

If you need to get a list of stored procedures in PostgreSQL, you can query PostgreSQL metadata. You can query for both functions and procedures using the information_schema.routines table. Here is an example:

SELECT *
FROM information_schema.routines
WHERE specific_schema = 'public';

Specifying the public schema will show us only those routines we’ve created.

In summary, stored procedures give you substantial power and flexibility when building a database. They allow you to package multiple SQL statements together to be run simultaneously. This improves performance, data integrity, and security for your database.