Pass in list of parameters for LIKE query into stored procedure

Question

I have a list of items that I would like to query on. The problem is that the number of items in the list is not constant. For example

select * from table1 where
field1 like @value1 + '%' OR
field1 like @value2 + '%'

I would like to pass value1, value2, etc into the stored procedure as a comma delimited string or something similar.

What have you tried? You could look into dynamic sql to potentially accomplish this. Depending on the nature of what you're actually returning full text indexing might be another option — Kritner, Jul 28 '14 at 17:33
[This answer](http://stackoverflow.com/a/4624483/124386) seems to cover all the options. — Richard Deeming, Jul 28 '14 at 17:34
This is easy to accomplish in Linq but the performance has been less than desirable. I figured a stored proc might have better performance. — Brian Kalski, Jul 28 '14 at 19:52
One way to do this is using table-valued parameters instead of using comma-seperated strings etc. — TT., Mar 15 '15 at 12:47

Hart CO · Answer 1 · 2014-07-28T17:40:11.990

0

If you stored the values one per row in a table variable you could simply JOIN, or better, use WHERE EXISTS:

SELECT DISTINCT a.* 
FROM  Table1 a
WHERE EXISTS (SELECT 1
              FROM @Table2 b
              WHERE a.field1 like b.value + '%')

edited Jul 28 '14 at 17:40

answered Jul 28 '14 at 17:34

Hart CO

34,064
6
48
63

score 0 · Answer 2 · answered Jul 28 '14 at 18:06

Here is a way you can pass a CSV to a stored proc, convert it to XML and use it in a join in your select.

Function to convert CSV to XML:

create function udf_CsvToXML(@Csv as varchar(8000),@Delim as varchar(15)=',')
returns xml
as
begin
    declare @xml as xml = CAST('<XML>'+('<X>'+REPLACE(@Csv,@Delim,'</X><X>')+'</X></XML>') AS XML)
    return @xml
end

Put the following in a stored proc, @Titles being a parameter instead of a declare:

declare @Titles varchar(8000) = NULL

    SET @Titles = ISNULL(@Titles, 'ALL')
    DECLARE @TitlesXML as XML

    if upper(@Titles) = 'ALL'
        SET @TitlesXML = (select distinct Title as X from LegalConfiguration for xml path(''), root('XML'))
    else
        SET @TitlesXML = dbo.udf_CsvToXML(@Titles,',')

    select Title
    from MonthlyTitlePerformance p
    join    (SELECT N.value('.[1]', 'varchar(25)') as value FROM @TitlesXML.nodes('/XML/X') as T(N)) tt
        on tt.value = p.Title

Pass in list of parameters for LIKE query into stored procedure

2 Answers2