Calling Stored Procedures

One of the major differences between a trigger and a stored procedure is that a stored procedure needs to be explicitly called. You cannot call a trigger. A stored procedure can be called from a number of places, such as client applications, autoexecution, other stored procedures and triggers, and tools like QA and EM. A stored procedure is executed using one of the following methods.

Specifying the Name of the Stored Procedure in the Statement

For example, the code sp_who and nothing else in the statement will execute the stored procedure by that name. There is one caveat to just naming the procedure in the procedure. The name must be the first line of code in your statement or batch…even if a billion lines of code follow it. If you stick a statement above the procedure name, the code will break.

Using the Clause Exec or Execute in Front of the Stored Procedure Name

The name is then followed by the rest of the stored procedure code. As long as you prefix the procedure with Exec or Execute, the code will execute.

The following statement is thus the preferred method:

 EXEC | EXECUTE sp_who

You can execute a stored procedure, as described here, that is grouped with a collection of stored procedures by specifying the procedure number assigned to the member procedure as follows:

 EXECUTE accpay_proc; 4

Calling a Stored Procedure Within INSERT

You can call a stored procedure in an INSERT statement, a topic that is further discussed in Chapter 20. What happens is that the result set returned from the stored procedure is inserted into the reference table. The following code provides an example of an INSERT … EXEC.

 INSERT INTO customers EXEC employee_customer