0

I am working on an indexer database that index the web, for a given search query I used to make a call to stored procedure then add the results from java

I am now trying to make it all in sql

I send multiple words to the stored procedure but it didn't get results. When using it with each word only it get normal results!

create procedure searchDictionary
@words nvarchar(max)
as
begin
    select distinct 
       WebPage.Address, WebPage.Title 
    from 
       WebPage 
    inner join 
       Paragraph on WebPage.ID = pageId 
    inner join 
       Dictionary on Dictionary.Id = keyword_id 
    where 
       Word in (@words);
end

exec searchDictionary N'protests, ukraine'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mohamed
  • 33
  • 1
  • 1
  • 9
  • 2
    See this question: http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause – Ken Keenan May 04 '14 at 21:45
  • 2
    This series of articles cover your problem exactly, make sure to read all 3, regardless of your SQL version: http://www.sommarskog.se/arrays-in-sql.html – Alejandro May 04 '14 at 21:51

1 Answers1

0

One way of doing it is to form up @words as a comma separated string in SQL:

eg:

"'a', 'b', 'c'"

and then use dynamic SQL and sp_execSQL to return your set.

create proc searchDictionary

@words nvarchar(max)
as

declare @SQL varchar(max)    

set @SQL = 'select distinct WebPage.Address, WebPage.Title from WebPage inner join Paragraph on WebPage.ID = pageId inner join Dictionary on Dictionary.Id = keyword_id where Word in (' + @words + ');'

exec sp_execSQL @SQL

GO
Jane S
  • 1,417
  • 16
  • 21
  • 2
    While this work for the simplest of scenarios, it has several problems, it's wide open to SQL inyection, it's insanely slow for larger lists and clutters the plan cache. Use this only as a last resort. – Alejandro May 04 '14 at 22:01
  • what exactly could be a sql injection, I mean what word may cause injection in this procedure – mohamed May 05 '14 at 05:12
  • Little Bobby Tables http://xkcd.com/327/ :) Seriously though, if you escape your string properly there would be no issue. But the performance is on large sets. If you only have a few items, then this method is ugly but it works. – Jane S May 05 '14 at 05:15