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
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.
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
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
Downloads