C# T-SQL create IN with array of strings

Question

Maybe I'm not doing something correctly, but I need to create a list of strings to filter some SQL SELECT query and all I can find are answers to escape a single parameter value only.

Given this array :

string[] domains = new string[] { "sec", "krn", "op", "ip" };

I need to create this query

SELECT * FROM user_domains WHERE name IN ("sec", "krn", "op", "ip")

Note : this is not a real query, but illustrate the point of this question.

A naïve solution would be to create a query as

SELECT * FROM user_domains WHERE name IN (@p1, @p2, @p3, @p4)

And executing the query with the params formatted as

Dictionary<string,string> inTerms = domains
     .Select((t, i) => new { Key = "p" + i, Term = t })
     .ToDictionary(item => item.Key, item => item.Term,
                   StringComparer.OrdinalIgnoreCase);

Is there another approach?

Update

Would it be just as easy and faster to perform a simple string comparison (aka index of) with a comma, or otherwise, separated string of terms?

Ex:

SELECT * FROM user_domains WHERE CHARINDEX(name, @terms) > 0

You have to do this in SQL Server? It's not possible to directly pass a comma seperated list to SQL Server. — JodyT
– JodyT, Commented Mar 18, 2013 at 11:19
Good question, I'm no DBA guru so I'm not sure if it's more optimal or not. I'll update the question. — Yanick Rochon
– Yanick Rochon, Commented Mar 18, 2013 at 11:22

stevepkr84 · Accepted Answer · 2013-03-18 11:19:48Z

I do this using a function

CREATE FUNCTION dbo.fnListToCol (
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(2) = '|' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

Then build a delimited parameter ( e.g "sec|krn" in your code and pass to a procedure like so:

WHERE ( ( VSSAS.Location IN ( SELECT * FROM  dbo.fnListToCol(@LocationFilter, '|') ) ) OR ( @LocationFilter IS NULL ) )

Collectives™ on Stack Overflow

C# T-SQL create IN with array of strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related