SQL Stored Procedures

Stored Procedures are the SQL equivalent of subroutines. They consist of a named sequence of statements that is stored in the database and can be executed by the CALL statement. A procedure can have arguments and return a result, which may be a single value or a result set (a set of rows such as that returned by a SELECT statement).

In addition to standard SQL statements to manipulate and report on the database, procedures can contain control statements such as IF/ELSE and WHILE.

Typically, a stored procedure is used to encapsulate a set of operations so that they can be executed easily and reliably. This allows for more modular programming, as a single stored procedure can be called any number of times by different application programs. In addition, stored procedures can reduce the amount of network traffic generated by an application. A single CALL statement can cause execution of many lines of SQL, and programming logic inside the procedure can reduce the amount of data returned to the client application.

Creating a Stored Procedure

You create a stored procedure with the CREATE PROCEDURE statement. You must declare any parameters required and the SQL statements that are run when the procedure is called. For example:

CREATE PROC EX5(@MIN DECIMAL(6,2) OUT, @MAX DECIMAL(6,2) OUT, @AVG DECIMAL(6,2) OUT) AS

    SELECT @MIN=MIN(AMT), @MAX=MAX(AMT), @AVG=AVG(AMT) FROM TESTDATA

To delete a stored procedure, use the DROP PROCEDURE statement. For example:

DROP PROCEDURE MYPROC

Additional Statements for use in Procedures

You can use any supported SQL statement in a stored procedure, plus the additional statements listed below:

DECLARE statement
SET statement
IF/ELSE statement
WHILE statement
BREAK statement
CONTINUE statement
BEGIN/END statements
RETURN statement

Calling a Stored Procedure

You call a stored procedure with the CALL statement. You must supply any parameters declared when the procedure was created. For example, you might call the procedure created in Creating a Stored Procedure above as follows:

DECLARE @A DECIMAL(6,2)

DECLARE @B DECIMAL(6,2)

DECLARE @C DECIMAL(6,2)

CALL EX5(@A,@B,@C)

The results of the procedure are then available in the three variables.

See Also

Writing Stored Procedures.