Microsoft SQL Server 2012 Bible

Returning Data from Stored Procedures

The previous section covered how to get data into a stored procedure. This section addresses how to get the data back out. SQL Server provides five means to return data from a stored procedure. You can use any combination of these options in a single stored procedure.

• Output parameters: Scalar data can be returned from a stored procedure with output variables.
• RETURN: A single integer value can be returned from a stored procedure with a RETURN statement.
• Result sets: A stored procedure can return data via one or more SELECT statements.
• RAISERROR or THROW: Informational or error messages can be returned to the calling application via RAISERROR or THROW.
• Table population: A table can be populated as part of stored procedure processing and then queried after execution.

Best Practice

With every operation that affects data, SQL Server, by default, also sends a message stating the number of rows affected or returned. In most cases, this information is not used by the calling application and is just extra information (and an extra server round trip). You can eliminate this message and improve performance by using SET NOCOUNT ON at the beginning of a stored procedure definition.

CREATE PROCEDURE schema.StoredProcedureName

AS

SET NOCOUNT ON;
...

Output Parameters

Output parameters enable a stored procedure to return data to the calling application. The keyword OUTPUT is required both when the procedure is created and when it is called. Within ...