DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to return multiple rows with OUTPUT?

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

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE