Receiving Data from Stored Procedures

A stored procedure would not be as marvelous a tool if it were unable to return data to you. SQL Server can return data to you in any supported data type, such as characters, strings, dates, images, user-defined data types, and even tabular data and cursors from SELECT statements.

You need to identify data intended for output with the OUTPUT keyword, which also has a short form OUT. This specifies the data intended for return to the client. The following stored procedure code returns a single integer value:

 CREATE PROC js_getatotal   @OrderNum   ©atotal int OUTPUT AS  SELECT ©atotal = total  FROM items WHERE OrderNum = @OrderNum

Returning Result Sets

Returning result sets to clients from stored procedures is simply a matter of coding a SELECT statement into the stored procedure. The tabulated or multirow data is returned automatically. If you need to perform complex SELECT routines (such as SELECT INTO) in your stored procedure, you can also enclose the SELECT statement into conditional or flow-control logic to prevent any result set from being returned to the client. The following code demonstrates a simple stored procedure that returns a result set to the client:

 CREATE PROCEDURE get customers_pastdue @past int=14 AS   SELECT CustID, CustName, Tell   FROM Customers WHERE pastdue = @past