You can only return a single row result set with OUTPUT.

You would have to call the stored procedure with additional parameters to get additional rows.

Multiple rows can be returned with a final SELECT in the stored procedure, stored in a staging table or XML OUTPUT parameter.

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the use of multiple rows return in an XML output parameter.

-- T-SQL Stored Procedure with XML output parameter
USE tempdb;
GO
 
CREATE PROC sprocXMLOutput
           @Data XML  OUTPUT
AS
  BEGIN
    DECLARE  @xml XML
    
    SET @xml = (SELECT ProductName = Name,
                       ListPrice
                FROM   AdventureWorks2008.Production.Product
                WHERE  ListPrice > 0.0
                for XML RAW)
    
    SET @Data = @xml
  END
 
GO
 
-- Execute stored procedure script
DECLARE  @Result XML
EXEC sprocXMLOutput
  @Result OUT
SELECT @Result
GO
 
/* Partial results
 
<row ProductName="LL Mountain Seat Assembly" ListPrice="133.3400" />
<row ProductName="ML Mountain Seat Assembly" ListPrice="147.1400" />
<row ProductName="HL Mountain Seat Assembly" ListPrice="196.9200" />
<row ProductName="LL Road Seat Assembly" ListPrice="133.3400" />
<row ProductName="ML Road Seat Assembly" ListPrice="147.1400" />
<row ProductName="HL Road Seat Assembly" ListPrice="196.9200" />
*/
 
-- Cleanup
DROP PROC sprocXMLOutput
 

GO

------------