Using Output Parameters

So far, you have used parameters to send data to the procedure. Now you will learn to return data from the procedure using an output parameter. We'll write a procedure, prGetCustomerCart, to return the Order ID for a shopping cart if one exists, or create a shopping cart if one doesn't exist. Then we'll call the prGetCustomerCart from inside the prAddItemToCart procedure and use an output parameter to pass the value back to the calling procedure.

Let's be clear on the logic for prGetCustomerCart. You pass it as a Customer ID and it returns an Order ID. To find the Order ID, it finds an open order for the customer (if one exists), or it creates a new open order. Listing 15.6 shows the completed prGetCustomerCart procedure.

Code Listing 15.6. Default Parameters Provide Additional Flexibility

 1: create proc prGetCustomerCart (
 2:     @CustomerID nchar(5),
 3:     @OrderID int output
 4: ) as
 5:
 6: /* find an open order if one exists */
 7: select @OrderID = OrderID
 8: from   Orders
 9: where  CustomerID = @CustomerID
10:
11: if @@error != 0 begin
12:     raiserror ('Error finding an open order', 16, 1)
13:     return 99     /* non-zero return means error */
14: end
15:
16: if @OrderID is null begin
17:     /* row not found so add a new open order */
18:     INSERT INTO [Orders] (
19:         [CustomerID],
20:         [EmployeeID],
21:         [OrderClosed]
22:     ) VALUES(
23:         'QUICK',
24:         2,
25:         'N'
26:     )
27:
28:     /* check for error in the insert */
29:     if @@error != 0 begin
30:         raiserror ('Error inserting a new order', 16, 1)
31:         return 99     /* non-zero return means error */
32:     end
33:
34:     /* Otherwise set OrderID = last identity value */
35:     set @OrderID = @@identity
36: end
37:
38: return
					

There are a couple of key points in the CREATE syntax. First, the parameter declaration in line 3 includes the keyword OUTPUT. In order to get a variable value to return to the user, you need to declare it with OUTPUT . (Note that if you declare a parameter for OUTPUT, it can still be used to accept values.)

Second, to actually output the variable, you don't need to do anything special. Just assign it a value as in line 35 and the value is passed back to the calling program.

Putting Output Parameters to Work

You can test the procedure directly from a SQL batch. You need to provide a local variable as a parameter in order to provide a container for returning the OrderID. The following code shows you how to use the stored procedure that was created and how to check the @ret value to see if the stored procedure worked.

declare @ret int
declare @MyCustomerID nchar(5)
declare @MyOrderID int
select @MyCustomerID = 'QUICK'
exec @ret = prGetCustomerCart @MyCustomerID, @MyOrderID output
if @ret = 0
    print 'The cart for '+ @MyCustomerID + 'is: '
        + cast(@MyOrderID as nchar(5))
else
    print 'Error'

The cart for QUICK is: 11078

When SQL Server executes the procedure, it retrieves the OrderID for CustomerID 'QUICK' and puts it into the @OrderID parameter. When the procedure returns, the value is assigned to the variable @MyOrderID by position.

Notice that the keyword, OUTPUT, is also included in the execution string. Both the procedure programmer and the user need to include OUTPUT in order for the value to be returned.

Communicating Between Stored Procedures

When we say that stored procedures communicate with each other, we really mean that stored procedures pass parameter values back and forth to each other. Now we can create the prAddItemToCustomerCart procedure. Instead of taking an OrderID for a parameter, the procedure accepts a CustomerID. Then it uses the prGetCustomerCart procedure to retrieve the active OrderID for the order detail insert. Listing 15.7 shows how this is done.

Code Listing 15.7. Modular Systems Often Call Procedures Within Other Stored Procedures

 1: create proc prAddItemToCustomerCart (
 2:     @CustomerID nchar(5),
 3:     @ProductID int,
 4:     @UnitPrice money,
 5:     @Quantity smallint,
 6:     @Discount real,
 7: /* OrderID is optional parameter;
 8:         can output OrderID if necessary */
 9:     @OrderID int = null output
10: ) as
11:
12: declare @ret int
13: exec @ret = prGetCustomerCart
14:     @CustomerID,
15:     @OrderID output
16:
17: if @ret != 0 begin
18: /* error getting an OrderID – quit now */
19:     return @ret
20: end
21:
22: INSERT INTO [Northwind].[dbo].[Order Details] (
23:     [OrderID],
24:     [ProductID],
25:     [UnitPrice],
26:     [Quantity],
27:     [Discount]
28: ) VALUES (
29:     @OrderID,
30:     @ProductID,
31:     @UnitPrice,
32:     @Quantity,
33:     @Discount
34: )
35:
36: /* check for error in insert */
37: if @@error != 0
38:     return 99
39: else
40:     return
						

There is not a lot of new material here. Perhaps the most important point is how important it is to check the return status of the inner procedure (prGetCustomerCart) lines 13 through 21, before going ahead with the insert. If you don't check the value, you could end up trying to insert a null or incorrect value for OrderID in Order Details.

Using Output Parameters with Remote Procedure Calls

Output parameters enable two-way communication between a stored procedure on one server (a local server) and a procedure on another server (a remote server).

For example, what if the Order Details table was stored on a local server, but the Orders table was on a remote server? In that case, the user or application would execute the prAddItemToCustomerCart procedure locally. When the procedure ran, it would call the prGetCustomerCart proc remotely, passing in a CustomerID and retrieving an OrderID. The OrderID would be returned as an output parameter.

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

To invoke a remote procedure, fully qualify the procedure name when it is called. In this case, the name includes the server name as well as database, owner, and object name. In Listing 15.7, we invoked the prGetCustomerCart procedure locally. To invoke the prGetCustomerCart procedure on another server, you would replace the execute statement in line 13 (leaving the parameters in lines 14 and 15) with the following statement:

execute @ret = OrderServer.Northwind.Northwind_DBO.prGetCustomerCart

There are a couple of setup and security issues to be resolved to implement remote procedures, but when they are implemented, the Remote Procedure Call (or RPC) mechanism provides a fast and reliable cross-server communications method.