O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required