Query an SQL field to see if it contains any of several values

Question

I want to query the rows of an SQL column to see if it contains any of several values.

For example, return the rows of a table where its column A contains any of the following words: ('cow','farmer','milk') .

It's easy enough when you know what words are, but I want to write sproc where I can feed any array of rows, and if column A for a particular row contains any of them, it will return the row.

I would like to be able to download:

('cow','farmer','milk')

or

('cow','farmer','steak','yikes')

or

 ('cow','farmer','three', 'pigs', 'wolf') 

It should be relatively simple, but I can't make life understand me. I'm on SQL Server 2008

Answer 1

One simple approach:

 declare @candidates varchar = '|cow|farmer|three|pigs|wolf|' select * from TableName where @candidates like '%|' + FieldName + '|%' 

The best way to do this in SQL 2008 is with a table parameter .

Answer 2

SQL Server does not know what an array is. You can write a split table-value function that turns each value into a row and then joins the base table, but this is definitely not optimal compared to Av's answer. Or you can check this based on a table parameter. TVP is definitely a better performer compared to all split methods, even the CLR .

 CREATE TYPE dbo.FarmItems AS TABLE(Item VARCHAR(32)); GO CREATE PROCEDURE dbo.FindFarmItems @List dbo.FarmItems READONLY AS BEGIN SET NOCOUNT ON; SELECT t.col1, t.col2, ... FROM dbo.table AS t INNER JOIN @List AS L ON t.columnA = L.Item; END GO 

Then in C # you just create a DataTable with your "array" and pass it to:

 DataTable dt = new DataTable(); dt.Columns.Add("Item", typeof(string)); dt.Rows.Add("cow"); dt.Rows.Add("farmer"); ... using (SqlConnection conn = new ...) { SqlCommand c = new SqlCommand("dbo.FindFarmItems", conn); 2.CommandType = CommandType.StoredProcedure; SqlParameter tvp = c.Parameters.AddWithValue("@List", dt); tvp.SqlDbType = SqlDbType.Structured; // execute, get a reader, etc... } 

Answer 3

You cannot pass arrays to stored procedures, since arrays do not exist in TSQL. You have several options, I give you two.

Option 1 - Quick and dirty (I do not recommend)
Pass the values ​​as a string and add it to the dynamic SQL statement.

 CREATE PROCEDURE MyProc @Values varchar(1000) AS DECLARE @SQL VARCHAR(2000) SET @SQL = 'SELECT blahblah WHERE SomeField IN (' + @Values + ')' -- Execute the statement and return the result END 

Besides the obvious SQL Injection vulnerability, this approach will not work for large collections, and it will not work too well. In addition, it will not work if any value contains a comma. I really do not recommend it, although it may be useful for quick testing.

Option 2 - More Flexible
Save all your values ​​in a temporary table that you specify in your stored procedure.

 CREATE TABLE #MyTempTable -- Fields... INSERT INTO #MyTempTable -- Insert the values CREATE PROCEDURE MyProc @Values varchar(1000) AS SELECT SomeFields FROM MyTable JOIN #MyTempTable ON -- Add join clause END 

This solution can scale better.

Like others, you can also use a table in memory (which I personally avoid, since I never succeeded with them, they always performed worse than temporary tables) or a table parameter, but you must declare its type in advance.

Answer 4

You can use the XML data type to pass list data to a stored procedure:

 create procedure dbo.LookItUp @idList xml as declare @lookup table ( id int not null ) insert @lookup (id) select distinct t.id.value('.','int') from @idList.nodes('/ids/id') as t( id ) where t.id is not null select * from dbo.my_data_table t join @lookup lu on lu.id = t.object_id return 0 go 

Easy!

Answer 5

I had the same dilemma, and I came up with the following. For example, if table_a contains ("My cow", "My goat", "My dog") and table_b contains ("Dog", "Cow"), then ...

 create table #table_a (field1 varchar(128)) insert into #table_a values('My Cow') insert into #table_a values('My Goat') insert into #table_a values('My Dog') create table #table_b (field1 varchar(128)) insert into #table_b values('Dog') insert into #table_b values('Cow') select * from #table_a table_a where (select count(*) from #table_b table_b where charindex(table_b.field1, table_a.field1) > 0) > 0 

returns ("My cow", "My dog")

Hope this helps.

Answer 6

You can simply use the WHERE IN column (rows separated by coma)

Here is my complete example of how to do this:

 create table #bar (foo varchar(20)) insert into #bar (foo) values('cow') insert into #bar (foo) values('cat') select foo from #bar where foo in ('cow','farmer','milk') drop table #bar