Handling array of parameters for WHERE-clause

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)

Handling array of parameters for WHERE-clause

Author

Topic

KJensen
Starting Member

12 Posts

Posted - 2006-07-16 : 05:57:09

Hi

Maybe I am just tired, but this one really has me puzzled, and I am hoping for some help. :)

I need write a stored procedure to do a search. Simple example:

DECLARE @SearchParam1 VARCHAR(100)
DECLARE @SearchParam2 VARCHAR(100)

SET @SearchParam1 = 'life'
SET @SearchParam2 = 'fear'

SELECT * 
FROM titles 
WHERE 
	((title LIKE '%' + @SearchParam1 + '%') OR (notes LIKE '%' + @SearchParam1 + '%'))
	AND
	((title LIKE '%' + @SearchParam2 + '%') OR (notes LIKE '%' + @SearchParam2 + '%'))
	--And repeat for any number of searchparameters...

My challenge is, that the user can input any number of search-params, and I need to do an AND-search for all parameters, like above.

The problem is not passing an array of parameters to a stored procedure, that is pretty trivial, and if I just used dynamic SQL (ie created a string), it would also be quite simple - but there must be a better way (I like to avoid using dynamic sql)?

Thanks in advance

- Klaus

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-16 : 06:34:24

If you put all your search words in a table you could do it like this (assuming you have an ID column in yout Titles table):

DECLARE @Titles TABLE (ID int, Title varchar(100), Notes varchar(100))

DECLARE @Words TABLE (Word varchar(100))

SELECT *
FROM @Titles T
INNER JOIN (
      SELECT ID
      FROM @Titles t
      INNER JOIN @Words w
         ON t.Title LIKE '%' + w.Word + '%' OR t.Notes LIKE '%' + w.Word + '%'
      GROUP BY ID
      HAVING COUNT(ID) = (SELECT COUNT(*) FROM @Words)) D
   ON T.ID = D.ID

It could be rewritten a bit if you want an output including ranked partial matches instead.

EDIT: Jeg har lige opdaget, at jeg kunne have svaret på dansk

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

KJensen
Starting Member

12 Posts

Posted - 2006-07-16 : 15:38:19

I have implemented the technique succesfully now, and it works great. Thanks (eller tak) :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources