Comma Separated Parameter in Stored Proc

[ninelgorb]

I have a comma separated input parameter @EmployeeId that contains the following values: '123','234','246'.

How can I break them up into a temp table(#Employees) so that I can then write a query against them. Something like this:

SELECT *
FROM SALES s
INNER JOIN #EMPLOYEES e
ON s.empId = e.empid

Thanks,
Ninel

 

[mrdenny]

Look at the substring and charindex functions in books online.

There are a bunch of threads in which we've gone over this.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0
WHILE @intLoopCounter <=999
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter +1
END

DECLARE @chvGroupNumbers VARCHAR(1000)
SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'
Insert into #EMPLOYEES
SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,
CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value
FROM NumberPivot
WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1
AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','
GO


Complete code is on my blog

http://sqlservercode.blogspot.com/2005/09/split-comma-delimited-string-fast.html

http://sqlservercode.blogspot.com/