A stored procedure is a series of SQL statements compiled and saved to the database.

Stored procedures can be as simple or as complex as you like. However, one of the benefits of stored procedures is that they allow you to store complex scripts on the server.

Stored procedures often contain conditional programming such as IF... ELSE statements for example. Stored procedures can also accept parameters.

Creating a Stored Procedure

Stored procedures are created using the CREATE PROCEDURE statement.

The exact syntax will depend on which DBMS you use.

For example, to create a stored procedure in SQL Server called GetCustomer, start with the following:

CREATE PROCEDURE GetCustomer AS

Follow that with the SQL code for the stored procedure.

If the stored procedure is to accept parameters, add these and their data type to the first line. Prefix the parameter name with the @ symbol.

For example to add a parameter called CustomerId with a datatype of Integer:

CREATE PROCEDURE GetCustomer @CustomerId int AS

Check with your DBMS documentation for the exact syntax to use for that DBMS.

Calling a Stored Procedure

You can call (or execute) a stored procedure with a single line of code.

In SQL Server, you could call the above stored procedure like this:

EXECUTE GetCustomer @CustomerId = 7

or

EXEC GetCustomer @CustomerId = 7

The above passes a parameter called CustomerId with a value of 7. Changing this value would process a different customer.

Advantages of Stored Procedures

Here are some of the main advantages of stored procedures:

Modular programming

You can write a stored procedure once, then call it again and again, from different parts of the application. You can even call it from multiple applications.

You can avoid duplicate code turning up all over an application by having the business logic handled in one place – the stored procedure. The application can then pass parameters to the stored procedure for processing, rather than processing it itself.

Performance

Stored procedures provide faster code execution and reduce network traffic.

• Faster execution: Stored procedures are parsed and optimised as soon as they are created and the stored procedure is cached. This means that it will execute a lot faster than sending a query from your application to the database. When you send a query from your application, it requires the database to compile and optimise your SQL code every time it runs.
• Reduced network traffic: If you send many lines of SQL code over the network to your database server, this will impact on network performance.  Running the code on the database server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.

Security

Users can execute a stored procedure without having to execute any of the statements directly.

Therefore, a stored procedure can provide advanced database functionality for users who wouldn’t normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Maintainability

If there’s a structural change to the database, if some tables get renamed for example, this need not break the application. As long as the stored procedure is updated to reflect the change, the application won’t break.