Pre-formated varchar variable does no work in select query I am trying to run a select query that receives in the where clause the @Sites variable. The query at the moment is returning empty because the following line 

AND S.[Name] IN (@Sites)


is not being processed as expected. Below an extract of the query:

SET QUOTED_IDENTIFIER OFF

DECLARE @Month int
, @Sites varchar(MAX) = null

SET @Month = 8
SET @Sites = 'Bayside;Collaborative'
SET @Sites = "'" + REPLACE(@Sites,';',"','") + "'"


SELECT 
    CV.[Id] AS Id
    , ...
    , S.[Name] AS [Site]
    , ...
  FROM 
    [CalendarViews] CV
    INNER JOIN [Sites] S ON S.[Id] = CV.[SiteId]
  WHERE
    MONTH(CV.[DateAllocation]) = @Month
    AND S.[Name] IN (@Sites)
  ORDER BY 
     S.[Name]


When I run the query replacing

AND S.[Name] IN (@Sites)


for

AND S.[Name] IN ('Bayside','Collaborative')


the query works as expected. Can anybody tell me what I am missing please? Thank you.
@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