Pre-formated varchar variable does no work in select query
@Sites is a string, not an array. So you are literally searching for:

S.[Name] IN ('''Bayside'',''Collaborative''')
-------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- this is one single string


I highly recommend, instead of passing in a comma- or semi-colon-separated string, you pass in a table-valued parameter. First, create this in your database:

CREATE TYPE dbo.Sites AS TABLE(Site NVARCHAR(255));


Then your procedure would say (leaving out the less relevant bits):

ALTER PROCEDURE dbo.ProcedureName
  @Sites dbo.Sites READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ...
  FROM dbo.Sites AS s
  INNER JOIN @Sites AS tvp
    ON s.[Name] = tvp.Site;
END
GO


Then your code would pass a structured parameter (I assume the set of sites originally comes from a DataTable or some other collection):



SqlCommand cmd = new SqlCommand("dbo.ProcedureName", conn);
c2.CommandType = CommandType.StoredProcedure;
SqlParameter tvp = cmd.Parameters.AddWithValue("@Sites", DataTableName);
tvp.SqlDbType = SqlDbType.Structured;
...


If you really want to do this the slow, old-fashioned way, you can create a UDF that splits the string:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '&lt;i&gt;' 
          + REPLACE(@List, @Delimiter, '&lt;/i&gt;&lt;i&gt;') 
          + '&lt;/i&gt;').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO


Then your query is (again leaving out the far less relevant bits):

  INNER JOIN dbo.SplitStrings_XML(@Sites, N';') AS f
    ON s.[Name] = f.Item;


Now, XML might not be the safe approach here, but it's the simplest to repro without offering even slower methods or auxiliary objects such as a numbers table. For other alternatives, and more on why you don't want to do it this way, see:


Split Strings The Right Way - Or The Next Best Way
Splitting Strings : A Follow-Up
Splitting Strings : Now with less T-SQL