Output parameters are a feature of stored procedures that is seldom used, which is a shame because they are an excellent option for returning scalar data to the user. In today's blog, we'll learn some uses for Output Parameters and how to use them in your stored procedures.
Syntax
The exact syntax for declaring parameters differs somewhat from one database vendor to another, so let's look at a couple of different examples. Here's one in SQL Server that simply parrots back the input parameter to the user:
In MySQL, there are slight differences in syntax, such as the IN/OUT parameters being located before the parameter names:
Some relational database (RDBMS), such as MySQL, support INOUT parameters. These are a combination of IN and OUT parameters, in that the calling program first passes in the INOUT parameter, and then, the stored procedure modifies it before sending the updated value to the calling program. Other RDMBS, such as SQL Server, treat OUT parameters like an INOUT by allowing them to be passed in to the procedure.
An Slightly More Complex Example (in MySQL)
The Sakila Sample Database was originally created as a learning tool for MySQL, but has since been ported to other DBMS as well. It's themed around a fictional video rental store, and contains a number of user functions and stored procedures. Some of these, such as the film_in_stock procedure, includes both IN and OUT parameters. Here is its definition in Navicat Premium:
The film_in_stock stored procedure determines whether any copies of a given film are in stock at a given store. As such, it declares two input parameters - the film ID and store ID - as well as an output parameter that relays the count of films in stock. A user function could have been employed for this purpose, but a procedure can also list the IDs of every film in stock. That's why there are two SELECT statements in the procedure body (between the BEGIN and END delimiters). The first SELECT fetches the film IDs, while the second populates the output parameter with the number of found rows.
Running the film_in_stock Stored Procedure
In Navicat, we can run a procedure directly from the designer via the Execute button. Clicking it brings up a dialog for entering input parameters:
A stored procedure may return multiple result sets and/or output parameters, so to deal with this, Navicat shows each in its own Result tab. The first tab shows the result set produced by the first query in the procedure, i.e., the inventory IDs of films that are in stock:
The second tab shows the count of films in stock at the store identified by the p_store_id input parameter (basically the number of rows returned by the first query):
Conclusion
In today's blog, we saw how the flexibility provided by the combination of input/output parameters and result sets makes stored procedures a truly powerful tool in the database developer's arsenal.
