First, create a quick function that will split a delimited list of values into a table, like this:
CREATE FUNCTION dbo.udf_SplitVariable
(
@List varchar(8000),
@SplitOn varchar(5) = ','
)
RETURNS @RtnValue TABLE
(
Id INT IDENTITY(1,1),
Value VARCHAR(8000)
)
AS
BEGIN
--Account for ticks
SET @List = (REPLACE(@List, '''', ''))
--Account for 'emptynull'
IF LTRIM(RTRIM(@List)) = 'emptynull'
BEGIN
SET @List = ''
END
--Loop through all of the items in the string and add records for each item
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List)-1)))
SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List) + LEN(@SplitOn), LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
Then call the function like this...
SELECT *
FROM A
LEFT OUTER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
WHERE f.Id IS NULL
This has worked really well on our project...
Of course, the opposite could also be done, if that was the case (though not your question).
SELECT *
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value
And this really comes in handy when dealing with reports that have an optional multi-select parameter list. If the parameter is NULL you want all values selected, but if it has one or more values you want the report data filtered on those values. Then use SQL like this:
SELECT *
FROM A
INNER JOIN udf_SplitVariable(@ExcludedList, ',') f ON A.Id = f.Value OR @ExcludeList IS NULL
This way, if @ExcludeList is a NULL value, the OR clause in the join becomes a switch that turns off filtering on this value. Very handy...