Multiple input variables in contains query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple input variables in contains query

Author

Topic

pizhang
Starting Member

4 Posts

Posted - 2003-09-19 : 19:30:20

I have created a procedure to select data from table:

@keyword1 varchar(15), @keyword2 varchar(15)
as
select * from table where contains(subject, '@keyword1 or @keyword2')

but this query returns nothing. yet I can run the query in Query analyser like this:
select * from table where contains(subject, 'apple or pear')

why this happen? Can we solve this problem? Now I am splitting the contains into two seperate query to get resutls:
select * from table where contains(subject, @keyword1)
select * from table where contains(subject, @keyword2)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-19 : 19:33:21

This is how you do the CONTAINS with an OR:

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "sasquatch ale" OR "steeleye stout" ')
GO

So your solution would be:

SELECT *
FROM table
WHERE CONTAINS(subject, ' "apple" OR "pear" ')

Tara

pizhang
Starting Member

4 Posts

Posted - 2003-09-19 : 19:38:21

I have no problem in inputing character literals, the problem is that when I use variables, no results returned.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-09-19 : 20:08:10

From BOL....

<contains_search_condition>

Specifies some text to search for in column. Variables cannot be used for the search condition.

Maybe you could use Dynamic SQL to make this work?

Using Tara's example


USE Northwind
GO

DECLARE @Keyword1 AS VARCHAR(50)
DECLARE @Keyword2 AS VARCHAR(50)
SET @Keyword1 = 'sasquatch ale'
SET @Keyword2 = 'steeleye stout'


DECLARE @SQL as VARCHAR(8000)

SELECT @SQL = 'SELECT ProductName '
SELECT @SQL = @SQL + 'FROM Products '
SELECT @SQL = @SQL + 'WHERE CONTAINS(ProductName, ''"' + @Keyword1 + '" OR "' + @Keyword2 + '"'')'


SELECT @SQL


EXEC(@SQL)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

pizhang
Starting Member

4 Posts

Posted - 2003-09-19 : 20:11:26

Yes, I don't have server at hand, but I believe this is the answer. Thank you Michael. The like '%' + @keyword + '%' works the same way, I should try that on this.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-09-19 : 20:14:15

I tried simply using string contatination on your Keywords, but that does not work. CONTAINS() does not allow for variables (which i think it dumb).


This does not work:

select * from table where contains(subject, '"' + @keyword1 + '" OR "' + @keyword2 + '"')

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

pizhang
Starting Member

4 Posts

Posted - 2003-09-19 : 20:25:10

Ok, still thanks. Actually, I tried for nearly one hour how to work this out, but failed. And that compelled me to use more queies to do this job. Yet I have got an better idea of doing this:

Passing an delimited array into SP, and split it in SP, then run a loop to search each element and insert the results into a temp table with only new IDs. Like this:

while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

insert into #temptable select * from mytable where contains(column,@array_value) where id not in (select id from #temptable)

select @array = stuff(@array, 1, @separator_position, '')
end

I am trying to use this to parsing keyword from input in sql server rather by by in web page and running sql query there. Yet I am wondering if this approach has more overhead. (Currently I only allow 4 keywords)

adweigert
Starting Member

22 Posts

Posted - 2003-09-19 : 21:34:45

One thing I think people constantly over look is the XML support in SQL Server 2000. When I generally need an unknown amount of data passed to SQL Server instead of passing a string I pass a TEXT value that is then parsed using SQL Server's XML procedures and read into a temp table where I can then use it in things like queries without doing a lot of CSV parsing. Its probably just as fast considering the fact that you have to take time to PARSE CSV strings and the XML parser is pretty efficient for a database.

Just a thought...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-09-19 : 21:51:42

Yeah, that SQL Server XML support works really well in v7.0....not. And passing text values around is always a joy, I don't get to use READTEXT and WRITETEXT often enough.

CSVs are as old as time, and just as universal. You don't have to "take time to PARSE CSVs" if you use the following:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

Already set up for you to use, just copy and paste.

Considering that 50% of your average XML content are tags or attribute names that get discarded, I don't see how the XML parser can be faster than a CSV parser that has to discard individual characters. CSVs certainly allow a lot more useful data to be passed in the same number of bytes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources