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.
CREATE PROCEDURE schema.StoredProcedureName AS SET NOCOUNT ON; ...
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 ...