question

My answer to this was to create a function that created a table. Since my input was from a web page the data came to me as a string delimited by commas. Therefore I created the following function:

/*****************************************************************
**** Parse A Comma Delimited String Into A Table
*****************************************************************/
CREATE FUNCTION [dbo].[ParseByComma] (
    @String VARCHAR(600) )
RETURNS @TblSubString TABLE
(
    VarSubString VARCHAR(50)
)
AS
BEGIN
    DECLARE @intPos INT,
            @SubStr VARCHAR(50)

    -- Remove All Spaces
    SET @String = REPLACE(@String, ' ','')
    -- Find The First Comma
    SET @IntPos = CHARINDEX(',', @String)
    -- Loop Until There Is Nothing Left Of @String
    WHILE @IntPos > 0
    BEGIN
        -- Extract The String
        SET @SubStr = SUBSTRING(@String, 0, @IntPos)
        -- Insert The String Into The Table
        INSERT INTO @TblSubString (VarSubString) VALUES (@SubStr)
        -- Remove The String & Comma Separator From The Original
        SET @String = SUBSTRING(@String, LEN(@SubStr) + 2, LEN(@String) - LEN(@SubStr) + 1)
        -- SET @String = REPLACE(@String, @SubStr + ',', '', 1)
        -- Get The New Index To The String
        SET @IntPos = CHARINDEX(',', @String)
    END
    -- Return The Last One
    INSERT INTO @TblSubString (VarSubString) VALUES (@String)
RETURN
END

This made me think about using a recursive CTE to generate a table from a delimited list. This could be useful along with the answers above to create a temporary table to join against.

DECLARE @string VARCHAR(100);
DECLARE @delimiter VARCHAR(2);
SET @string = 'one,two,three';
SET @delimiter = ',';

-- make sure the string ends with a delimiter
SET @string = @string+@delimiter;

WITH listToTable AS (
    SELECT CHARINDEX(',',@string,1) pos 
    	, CAST(SUBSTRING(@string, 0, (CHARINDEX(',',@string,0)-0)) AS varchar(max)) piece
    UNION ALL 
    SELECT CHARINDEX(',',@string,(prev.pos+1)) pos 
    	, CAST(SUBSTRING(@string,(prev.pos+1), (CHARINDEX(',',@string,(prev.pos+1))-(prev.pos+1))) AS varchar(max)) piece
    FROM listToTable prev
    WHERE CHARINDEX(',',@string,(prev.pos+1))>prev.pos
)
SELECT piece FROM listToTable WHERE piece != '';

You cannot use a parameter in an IN clause but you can get the same result by checking that the parameter includes the value by building a LIKE clause.

DECLARE @Vendor varchar(1000), @VendorID varchar(1000)
SET @Vendor = 'Smith'
SET @VendorID = ''

IF  @Vendor = 'Smith' 
SET @VendorID = ' ''200711'', ''200794'', ''218204'', ''213601'', 
''213602'', ''213610'', ''213603'', ''213604'', ''213609'', ''213605'',
''21360'', ''213607'', ''213608'',''198502'', ''198501'', ''198503'',
''218201'',''229601'', ''215401'' '

SELECT 
  d.strName As Department,
  f.strName As Facility,
  v.strName As Vendor, 
  vh.strHostVendorID As VendorID

FROM   dbo.tblControlLog c

INNER JOIN dbo.tblControlLogPurchaseOrders po
  ON c.guidControlLogID=po.guidControlLogID 

INNER JOIN dbo.tblFacilities f
  ON c.guidFacilityID=f.guidFacilityID 

INNER JOIN dbo.tblDepartments d
  ON c.guidDepartmentID = d.guidDepartmentID

INNER JOIN dbo.tblVendorHost vh
  ON po.guidVendorHostID=vh.guidVendorHostID 

INNER JOIN dbo.tblVendors v
  ON po.guidVendorID=v.guidVendorID 

WHERE
  (@VendorID = 'All' OR @VendorID like '%'''+cast(vh.strHostVendorID as varchar(10))+'''%')