Stored Procedure in SQL

Introduction

Stored procedures are a crucial part of SQL databases. They consist of prepared SQL code that you can save and reuse. This feature helps avoid writing the same queries repeatedly. You can call the stored procedure to execute the saved code. Additionally, stored procedures can accept parameters, making them versatile and dynamic. This article will cover all about stored procedures in SQL including its definition, implementation, and use cases.

Overview

• Understand what stored procedures are in SQL.
• Know how and when to use stored procedures in SQL.
• Learn to use parameters in stored procedures.
• Learn to implement stored procedures in SQL Server, Oracle, MySQL, and PostgreSQL.

Definition and Benefits of Stored Procedures

A stored procedure is a set of SQL statements that perform a specific task. You save these procedures and run them as needed, making your SQL operations more efficient and organized. Stored procedures work like functions in programming, allowing you to run predefined actions easily. They offer many benefits: you can reuse code, improve performance since they run faster than individual SQL statements, enhance security by controlling user access, and simplify maintenance by centralizing your SQL code for easier updates and debugging.

Creating Stored Procedures

Creating a stored procedure is straightforward. The basic syntax involves the CREATE PROCEDURE command followed by the procedure name and the SQL statements it contains. Here’s a simple example:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Example in SQL Server

In SQL Server, creating a stored procedure might look like this:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

This procedure selects all records from the Customers table.

Example in MySQL

In MySQL, the syntax is slightly different. Here’s how you create a similar procedure:

DELIMITER //
CREATE PROCEDURE SelectAllCustomers()
BEGIN
SELECT * FROM Customers;
END //
DELIMITER ;

MySQL uses DELIMITER to define the start and end of the procedure.

Example in PostgreSQL

In PostgreSQL, you use the LANGUAGE keyword:

CREATE PROCEDURE SelectAllCustomers()
LANGUAGE SQL
AS $$
SELECT * FROM Customers;
$$;

This command creates a procedure to select all customers.

Example in Oracle

Creating a procedure in Oracle involves a bit more syntax:

CREATE PROCEDURE SelectAllCustomers
AS
BEGIN
OPEN res FOR SELECT * FROM Customers;
DBMS_SQL.RETURN_RESULT(res);
END;

Oracle uses cursors to handle the result set.

Executing Stored Procedures

Executing a stored procedure is simple. You use the EXEC command followed by the procedure name in SQL Server and Oracle. In MySQL and PostgreSQL, you use the CALL command.

Example in SQL Server

In SQL Server, you execute a stored procedure like this:

EXEC SelectAllCustomers;

This command runs the procedure and retrieves all customers.

Example in MySQL

In MySQL, you use the CALL command:

CALL SelectAllCustomers();

This command performs the same task as the SQL Server example.

Example in PostgreSQL

In PostgreSQL, the execution is similar to MySQL:

CALL SelectAllCustomers();

This command calls the procedure to select all customers.

Example in Oracle

In Oracle, the execution involves the EXEC command:

EXEC SelectAllCustomers;

This command runs the procedure and returns the customer data.

Using Parameters in Stored Procedures

You can pass parameters to stored procedures to make them more dynamic. For instance, you might want to filter results based on a specific value. This can be done using parameters.

Single Parameter

Here’s how you can create a stored procedure with a single parameter.

CREATE PROCEDURE SelectCustomersByCity
@City NVARCHAR(30)
AS
SELECT * FROM Customers WHERE City = @City;
GO;

In this example, the procedure selects customers from a specified city.

Multiple Parameters

Stored procedures can also accept multiple parameters. This allows for more complex queries. You simply list each parameter, separated by commas.

CREATE PROCEDURE SelectCustomersByCityAndPostalCode
@City NVARCHAR(30), @PostalCode NVARCHAR(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode;
GO;

This procedure filters customers based on city and postal code.

Example with Parameters in SQL Server

In SQL Server, you might execute a procedure with parameters like this:

EXEC SelectCustomersByCity @City = 'London';

Or, with multiple parameters:

EXEC SelectCustomersByCityAndPostalCode @City = 'London', @PostalCode = 'WA1 1DP';

Example with Parameters in MySQL

In MySQL, you define and call procedures with parameters like this:

DELIMITER //
CREATE PROCEDURE SelectCustomersByCity(IN City NVARCHAR(30))
BEGIN
SELECT * FROM Customers WHERE City = City;
END //
DELIMITER ;
CALL SelectCustomersByCity('London');
With multiple parameters:
DELIMITER //
CREATE PROCEDURE SelectCustomersByCityAndPostalCode(IN City NVARCHAR(30), IN PostalCode NVARCHAR(10))
BEGIN
SELECT * FROM Customers WHERE City = City AND PostalCode = PostalCode;
END //
DELIMITER ;
CALL SelectCustomersByCityAndPostalCode('London', 'WA1 1DP');

Example with Parameters in PostgreSQL

In PostgreSQL, you use a similar approach:

CREATE PROCEDURE SelectCustomersByCity(IN City NVARCHAR(30))
LANGUAGE SQL
AS $$
SELECT * FROM Customers WHERE City = City;
$$;
CALL SelectCustomersByCity('London');
With multiple parameters:
CREATE PROCEDURE SelectCustomersByCityAndPostalCode(IN City NVARCHAR(30), IN PostalCode
NVARCHAR(10))
LANGUAGE SQL
AS $$
SELECT * FROM Customers WHERE City = City AND PostalCode = PostalCode;
$$;
CALL SelectCustomersByCityAndPostalCode('London', 'WA1 1DP');

Example with Parameters in Oracle

In Oracle, single parameters are handled as follows:

CREATE PROCEDURE SelectCustomersByCity(City IN NVARCHAR2)
AS
BEGIN
OPEN res FOR SELECT * FROM Customers WHERE City = City;
DBMS_SQL.RETURN_RESULT(res);
END;
EXEC SelectCustomersByCity('London');

With multiple parameters:

CREATE PROCEDURE SelectCustomersByCityAndPostalCode(City IN NVARCHAR2, PostalCode IN NVARCHAR2)
AS
BEGIN
OPEN res FOR SELECT * FROM Customers WHERE City = City AND PostalCode = PostalCode;
DBMS_SQL.RETURN_RESULT(res);
END;
EXEC SelectCustomersByCityAndPostalCode('London', 'WA1 1DP');

Managing Stored Procedures

Now let’s learn how to modify and manage stored procedures in SQL.

Modifying Stored Procedures

You can modify a stored procedure using the ALTER PROCEDURE command. This will allow you to change the procedure without dropping and recreating it.

ALTER PROCEDURE SelectAllCustomers
AS
SELECT CustomerName, ContactName FROM Customers;
GO;

This command updates the procedure to select only the CustomerName and ContactName columns.

Dropping Stored Procedures

If you no longer need a stored procedure, you can drop it using the DROP PROCEDURE command.

DROP PROCEDURE SelectAllCustomers;

This command removes the procedure from the database.

Advantages of Stored Procedures

There are many advantages of using stored procedures in SQL, some of which are:

• Reusability: Stored procedures promote reusability. You can write the SQL code once and use it multiple times. This saves time and reduces errors.
• Performance Improvement: Stored procedures improve performance. They are precompiled, so they execute faster than individual SQL statements.
• Security Benefits: Stored procedures enhance security. You can grant users permission to execute procedures without giving them direct access to the data.
• Maintenance and Debugging Ease: Stored procedures simplify maintenance and debugging. By centralizing your SQL code, you make it easier to update and debug.

Best Practices for Managing Stored Procedures

When managing stored procedures, follow these best practices:

1. Use Naming Conventions: Consistent naming conventions make it easier to manage procedures.
2. Document Your Procedures: Include comments in your code to explain what each procedure does.
3. Optimize Performance: Regularly review and optimize your procedures for performance.
4. Version Control: Use version control systems to track changes to your procedures.

Common Use Cases

Here are some of the most common use cases of stored procedures in SQL.

Data Retrieval

Stored procedures are great for fetching data. They run complex queries to get specific results. This is useful for reports and analysis. For example, you can get a list of all customers who bought something last month.

Data Modification

You can use stored procedures to change data in SQL databases. They help with adding, updating, or deleting records. This ensures the changes follow rules and keep data correct. For instance, you might update a customer’s address using a stored procedure.

Complex Business Logic Implementation

Stored procedures handle complex business rules well. They can do calculations, check data, and enforce rules. This keeps everything organized and easy to manage. For example, a stored procedure can calculate total sales and apply discounts based on the amount sold.

Learn more about using SQL For Data Science with this beginner’s guide.

Conclusion

Stored procedures are strong tools in SQL databases. They help reuse code, boost performance, improve security, and make maintenance easier. Use stored procedures to get, change, and handle data. Follow best practices to keep them efficient and simple to manage. Use stored procedures to simplify your database work and make your SQL code better and easier to maintain.

Master SQL with SQL: A Full Fledged Guide from Basics to Advance Level.

Frequently Asked Questions