Hello,
Heres how I handle a list of Ids as a parameter in SQL. First I have a function that converts a delimiter seperated string of Id's into a table.
CREATE FUNCTION fnGetNumericTableFromCommaSeparatedList
(
@CommaSeparatedList VARCHAR(4000),
@Delimiter CHAR
)
RETURNS @ListValues TABLE
(
Id BIGINT NULL
)
AS
BEGIN
DECLARE @CommaIndex INT
DECLARE @TextValue VARCHAR(255)
SET @CommaIndex = CHARINDEX(@Delimiter, @CommaSeparatedList)
WHILE @CommaIndex > 0
BEGIN
SET @TextValue = LTRIM(RTRIM(SUBSTRING(@CommaSeparatedList, 1, @CommaIndex - 1)))
IF(@TextValue != '' AND (ISNUMERIC(@TextValue) = 1))
BEGIN
INSERT @ListValues (Id) VALUES (CONVERT(BIGINT, @TextValue))
END
IF(@TextValue = 'NULL')
BEGIN
INSERT @ListValues (Id) VALUES (NULL)
END
SET @CommaSeparatedList = SUBSTRING(@CommaSeparatedList, @CommaIndex + 1, LEN(@CommaSeparatedList))
SET @CommaIndex = CHARINDEX(@Delimiter, @CommaSeparatedList)
END
IF (@CommaSeparatedList != '' AND (ISNUMERIC(@CommaSeparatedList) = 1))
BEGIN
INSERT INTO @ListValues (Id) VALUES (CONVERT(BIGINT, @CommaSeparatedList))
END
IF(@CommaSeparatedList = 'NULL')
BEGIN
INSERT @ListValues (Id) VALUES (NULL)
END
RETURN
END
Then I join to the function
DECLARE @ListOfIds VARCHAR(255)
SET @ListOfIds = '1, 3, 5, 6, 6'
SELECT f.*
FROM Foo f
INNER JOIN dbo.fnGetNumericTableFromCommaSeparatedList(@ListOfIds, ',') l
ON f.Id = l.Id
Hope that helps,
Matt