Microsoft SQL Server - Returning Result Sets from Stored Procedures

You can use a standalone SELECT statement (without INTO clause, or SET expression) to return a result set from a T/SQL stored procedure.

Example (SQL Server):

CREATE PROCEDURE dbo.GetOrderInfo @data INT
AS
  SELECT * FROM dbo.OrderInfo WHERE data = @data;

Returning Result Sets - Differences between SQL Server and other Databases

Database	Returning Result Sets
Oracle	Oracle result sets - OPEN cursor statement and refcursor OUT parameter