In this article I will explain with an example how to return multiple values from a Stored Procedure in SQL Server.
Multiple values will be returned from Stored Procedure by returning comma separated (delimited) values using Output Parameter.
Output Parameter is supported in Stored Procedures of all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012 and 2014.
 
Concept
In my previous article I have explained Return Value from Stored Procedure in SQL Server, but the problem is that SQL Server can return only a single INTEGER value using the RETURN keyword.
Hence I have used an Output parameter and returned multiple comma separated (delimited) values through it.  The comma separated (delimited) values can be easily split and converted into rows using Split function.
 
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Download and install Northwind Database
 
Return multiple values from Stored Procedure in SQL Server
The following Stored Procedure accepts two parameters, City (Input parameter) and EmployeeIds (Output parameter).
It returns the ID of all Employees based on the City. The returned Employee Ids are separated (delimited) by comma using the COALESCE or ISNULL functions in SQL Server.
Using COALESCE
CREATE PROCEDURE GetEmployeesByCity
      @City NVARCHAR(15)
      ,@EmployeeIds VARCHAR(200) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
 
      SELECT @EmployeeIds = COALESCE(@EmployeeIds + ',', '') + CAST(EmployeeId AS VARCHAR(5))
      FROM Employees
      WHERE City = @City
END
 
 
Using ISNULL
CREATE PROCEDURE GetEmployeesByCity
      @City NVARCHAR(15)
      ,@EmployeeIds VARCHAR(200) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
 
      SELECT @EmployeeIds = ISNULL(@EmployeeIds + ',', '') + CAST(EmployeeId AS VARCHAR(5))
      FROM Employees
      WHERE City = @City
END
 
 
 
Fetching multiple returned values from Stored Procedure
In order to fetch the multiple returned values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
DECLARE @EmployeeIds VARCHAR(200)
EXEC GetEmployeesByCity 'London', @EmployeeIds OUTPUT
SELECT @EmployeeIds
 
Output
Return multiple values from Stored Procedure in SQL Server example
 
You can also make use of the Split function to split the comma separated (delimited) values into rows.
DECLARE @EmployeeIds VARCHAR(200)
EXEC GetEmployeesByCity 'London', @EmployeeIds OUT
SELECT Item AS EmployeeId FROM dbo.SplitString(@EmployeeIds, ',')
 
Output
Return multiple values from Stored Procedure in SQL Server example
 
 
Downloads
GetEmployeesByCity.sql