With Stored Procs we have found that you have to use a split string seperation function. Thats why when you took the actual select code and put it in the stotred proc, that worked. This is what our company does:
From the Internet (dont know who author is) we use this:
ALTER FUNCTION [dbo].[fn_SplitStringList]
(
@StringList VARCHAR(MAX)
)
RETURNS @TableList TABLE( StringLiteral VARCHAR(128))
AS
BEGIN
DECLARE @StartPointer INT, @EndPointer INT
SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)
WHILE (@StartPointer < LEN(@StringList) + 1)
BEGIN
IF @EndPointer = 0
SET @EndPointer = LEN(@StringList) + 1
INSERT INTO @TableList (StringLiteral)
VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer,
@EndPointer - @StartPointer))))
SET @StartPointer = @EndPointer + 1
SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)
END -- WHILE
RETURN
END
Then in stored proc with multivalue params we write the code like this, example:
WHERE.....
AND (ld.DESTCITY IN (select * from [dbo].[fn_SplitStringList](@DestCity))