Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Also, I suggest against ever using QUOTED_IDENTIFIER OFF - just get used to doubling-up single quotes when you need them. In this case, you don't... –  Aaron Bertrand Aug 15 at 12:21

1 Answer 1

up vote 7 down vote accepted

@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, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').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:

share|improve this answer
1  
Thank you @Aaron, you are amazing!, I would love to use the first option, unfortunately, i cannot modify the procedure called from my C# code. I'll have it in mind for future usage though. I am using the second option you suggested and it works perfect for what I need. –  Francisco Aug 15 at 3:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.