In this article I will explain with an example, how to pass comma separated (delimited) values as Parameter to Stored Procedure in SQL Server.
The comma separated (delimited) values will be split into Table rows and will be used for querying inside the SQL Server Stored Procedure.
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below.
The SplitString function
The following script has to be executed. The SplitString function is a Table-Valued function i.e. it returns a Table as output and accepts two parameters namely:
1. @Input – The string to be split.
2. @Character – The character that is used to separate the words or letters.
CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END
RETURN
END
GO
Passing comma separated (delimited) values to SQL Query
The following SQL query split the string separated (delimited) by comma.
SELECT Item
FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')
Output
![Pass comma separated (delimited) values as Parameter to Stored Procedure in SQL Server]()
Passing comma separated (delimited) values as Parameter to Stored Procedure
The following Stored Procedure needs to be created which will accept the string separated (delimited) by comma.
CREATE PROCEDURE GetEmployees
@EmployeeIds VARCHAR(100)
AS
BEGIN
SELECT FirstName, LastName
FROM Employees
WHERE EmployeeId IN(
SELECT CAST(Item AS INTEGER)
FROM dbo.SplitString(@EmployeeIds, ',')
)
END
Executing the Stored Procedure
The Stored Procedure is executed which finally returns the FirstName and LastName records of Employees for which the Ids are passed using a string separated (delimited) by comma.
EXEC GetEmployees '1,3,5,9'
Output
![Pass comma separated (delimited) values as Parameter to Stored Procedure in SQL Server]()
Downloads
