Dynamically adjust stored procedure with one variable holding multiple values

Question

I have a stored procedure that takes an input parameter as follows:

@Name NVARCHAR(50) = 'George W Bush'

How can I adjust my where clause dynamically:

SELECT * FROM TABLE
WHERE CONTAINS(FIELD, 'George')
  AND CONTAINS(FIELD, 'W')
  AND CONTAINS(FIELD, 'Bush')

Or for example:

@Name NVARCHAR(50) = 'Harry Potter'

SELECT * FROM TABLE
WHERE CONTAINS(FIELD, 'Harry')
  AND CONTAINS(FIELD, 'Potter')

I have found many solutions here that come so close to doing what I need, but I specifically need for the field to contain each substring of the input parameter.

The substrings are always space-delimited? – Ann L. Jul 18 '16 at 19:30 — Ann L., Jul 18 '16 at 19:30
Yes they will always be space-delimited. – Jake Jul 18 '16 at 19:34 — Jake, Jul 18 '16 at 19:34

score 4 · Accepted Answer · answered Jul 18 '16 at 19:47

4

CONTAINS can work with multiple words with AND or OR conditions like this:

 WHERE CONTAINS (FIELD, 'George AND W AND Bush')

So what you can do is change your variable to(given they will always be space-delimited):

DECLARE @Name NVARCHAR(50)
SET @Name = REPLACE('George W Bush', ' ', ' AND ')

SELECT * FROM TABLE
WHERE CONTAINS(FIELD, @Name)

answered Jul 18 '16 at 19:47

xbb

2,073
1
19
34

I ended up having the following issue after I created a full-text index for my table: http://stackoverflow.com/questions/3719099/null-or-empty-full-text-predicate-when-executing-a-store-procedure-from-a-boun However, this solution worked the best for me. Thank you very much. – Jake Jul 20 '16 at 18:35

S3S · Answer 2 · 2016-07-18T19:56:21.300

Here is a way to split your names, regardless if it is FirstName LastName, or FirstName MiddleName LastName, into parameters which you can use. Of course if you expect more than 2 spaces (three names) this won't work

DECLARE @Name NVARCHAR(50) = 'George W Bush' 
DECLARE @Spaces int = (SELECT LEN(@Name)-LEN(REPLACE(@Name, ' ', '')))

DECLARE @FirstName varchar(50) = null
DECLARE @MiddleName varchar(50) = null
DECLARE @LastName varchar(50) = null

IF @Spaces = 2 
    BEGIN
        SET @FirstName = LEFT(@Name,CHARINDEX(' ',@Name))
        SET @MiddleName = SUBSTRING(@Name,CHARINDEX(' ',@Name) + 1,1)
        SET @LastName = RIGHT(@Name,CHARINDEX(' ',REVERSE(@Name)))
    END
ELSE IF @Spaces = 1 
    BEGIN
        SET @FirstName = LEFT(@Name,CHARINDEX(' ',@Name))
        SET @LastName = RIGHT(@Name,CHARINDEX(' ',REVERSE(@Name)))
    END


SELECT @FirstName, @MiddleName, @LastName

Ann L. · Answer 3 · 2016-07-18T20:07:42.470

CONTAINS will work if your table is full-text indexed, but if it isn't, consider this:

-- Here's my test data
declare @t table
(
   text nvarchar(max)
)

insert @t values('I''m just wild about Harry')
              , ('Sally Potter is important in British theater')
              , ('I love the "Harry Potter" books')


-- Here are our keywords:
declare @Name varchar(max) = 'Harry Potter'

-- To make this work, we need to convert the substrings into tabular form.  
-- so here's how we'll do that. 

declare @NameXml xml 

set @NameXml = convert(xml, '<substrings><substring>' + 
               replace(@Name, ' ', '</substring><substring>') +
               '</substring></substrings>')

-- That gives us an XML variable with each substring as a separate 
-- element.
;
with mockTable as 
(
    select  Keyword = N.a.value('.[1]', 'varchar(100)')
    from    @NameXml.nodes('/substrings/substring') as N(a)
)
-- Now that we have a "mock table", we can use the ALL operator to 
-- confirm that each string in our mock table is found in the
-- records we return.
select  *
from    @t
where   1 = ALL (
    select  case 
            when [text] like '%' + Keyword + '%'
            then 1
            else 0
            end
    from mockTable
    )

score 0 · Answer 4 · answered Jul 18 '16 at 22:24

Like Ann L. said, CONTAINS and FREETEXT only work when you've already created the FULLTEXT INDEX on the underlying table. As per you need this string-parsing operation, why not create a user-defined function to handle that, and use it within your Sproc. BTW, I really dont want to use wild cards. :)

CREATE FUNCTION [dbo].[parsing]
(
    @String NVARCHAR(2000),
    @Delimiter NVARCHAR(5)
)
RETURNS @Rtn TABLE
(
    ID INT IDENTITY(1,1),
    Seg NVARCHAR(50)
)
AS
BEGIN
    WHILE(CHARINDEX(@Delimiter,@String)>0)
    BEGIN
        INSERT INTO @Rtn (Seg)
        SELECT LTRIM(RTRIM(SUBSTRING(@String,1,CHARINDEX(@Delimiter,@String)-1)))
        SET @String = SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+LEN(@Delimiter),LEN(@String))
    END
    INSERT INTO @Rtn (Seg)
    SELECT LTRIM(RTRIM(@String))
END

Dynamically adjust stored procedure with one variable holding multiple values

4 Answers4