Now, make sure that the row was really inserted:

select *
from   orders
where order_id = 1

order_id    item_id     qty         unit_price   ext_price
----------- ----------- ----------- ------------ ----------
1           2           15          15.00        225.00

The row returned shows that the proper information was inserted, and the columns affected by price include the right values.

Output Parameters and RPCs

Sometimes the need for output parameters is difficult to understand. After all, we’re already returning data from a stored procedure with a SELECT statement. And we could easily have consolidated the two procedures into a single stored procedure, completely eliminating the need for the output parameter.

Tomorrow you will learn more about remote procedure calls (RPCs), in which a user application or SQL batch can execute a stored procedure on a remote SQL Server.

A remote procedure call (RPC) is the execution of a stored procedure on a remote server. A remote server is a server other than the server a client directly logged into.

For example, an organization could set up a central pricing server that no user could directly log into. The only method of accessing the prices stored there would be through RPCs executed from a local server to the remote price server. Figure 16.2 shows the relationship between the client application, the local server, and the remote server.

Figure 16.2.  A client application executes a SQL batch that runs a remote procedure on a remote server.

Here’s the crucial point: the results of a SELECT statement in the remote procedure are returned directly to the calling program (that is, the client application). The output parameter would be the only method of returning a value to the calling procedure or batch; that value could be put to use immediately.

Procedure Return Status

We’ve covered a lot of ground today, and we only have one more topic before we finish up—procedure return status. So far we have been writing very optimistic code: no error checking, no validations. And there’s plenty that could go wrong here.

Let’s look closely at the system of procedures we built to look up an item price and insert an order. What if the item didn’t exist online, or if there were no price for that item (price can be null in the items table)? We certainly shouldn’t insert an order, and the user or her application should be warned. What if we couldn’t access the procedure (especially a remote procedure) because of database problems, network problems, and so forth? Again, you can’t insert an order without a price.

All of these circumstances are handled with the procedure return status.

The procedure return status describes the state of the procedure when it ended. It tells the calling program why the procedure stopped executing.

Under normal circumstances, a procedure ends its execution because it reaches the end of the procedure or the procedure executes the RETURN statement. This normal end of a procedure has a return status of 0 by default. Almost all SQL Server applications have agreed to use this value of 0 to mean that the procedure executed successfully.

What do other return status values mean? Microsoft has reserved the block of numbers from -1 to -99 for its own use, and has assigned a specific meaning to the first 14 values in that range. Table 16.1 gives those values and their meaning.

It’s important to think about what these return status values mean, and when they might occur under normal operation. SQL Server may need to terminate the execution of your procedure before you execute a RETURN statement. If that’s necessary, it returns this status value to let you know why. For example, let’s try running the pr_insertorder procedure, but pass it an incorrect parameter value. I will retrieve the return status by setting a variable equal to the procedure name:

declare @retstatus int
declare @order_number int
exec @retstatus = pr_insertorder “2”, 15, @order_number output
select @order_number “Your order number”, @retstatus “Return status”

Msg 257, Level 16, State 1
Implicit conversion from datatype ‘varchar’ to ‘int’ is not allowed.
Use the CONVERT function to run this query.
Your order number Return status
---------------- ------------
(null)            -6

By wrapping the value 2 in quotes, I’ve passed it to the procedure as a string. This caused a data type mismatch when the procedure tried to assign that string value to an integer parameter. The mismatch returned an error message (which is returned to the client program, not to a calling batch, by the way). It also caused the stored procedure to end unexpectedly, setting the return status to the value, –6. I was able to retrieve the return status by declaring the @retstatus variable and then setting it equal to the procedure name in the EXECUTE statement.

This is fine for the serious system-level problems that are captured by the server itself, but what about application-level errors that are only a problem because of my business rules? How do users warn programs that a stored procedure failed for any of those reasons?