stored procedure query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

stored procedure query

Author

Topic

fogofogo
Starting Member

11 Posts

Posted - 2008-10-25 : 13:43:39

hello

I've written a stored procedure that searches a table based on any number of keywords.

I parse the keywords parameter into a temp table and then search based on a comparison between a search column and the keyword temp table.

Everything works fine but I would like an inclusive search where each keyword row in my temp table must be present in my search column of every record returned.

SELECT DISTINCT *
FROM SearchTable st, #KeywordTable kt
WHERE (st.Search LIKE kt.Keyword)
ORDER BY DateCreated DESC

I would like the search to be inclusive of all keywords, has anyone got a suggestion how this can be achieved?
I know I could do this through server code but I wish to avoid SQL injection attacks and speed is important.
Also, Full Text Search isn't a possible solution at the moment as I also need to perform other none search field comparisons.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 14:03:34

is this what you're looking at?

SELECT DISTINCT *
FROM SearchTable st
INNER JOIN #KeywordTable kt
ON st.Search LIKE '%' + kt.Keyword + '%'
ORDER BY DateCreated DESC

fogofogo
Starting Member

11 Posts

Posted - 2008-10-26 : 14:28:49

Thanks for your answer.

This fixed it for me...

Solution by Borislav Borissov:

DECLARE @MyTable TABLE (Id int, Searched varchar(200))
DECLARE @Keys TABLE (Word varchar(200), Id int)

INSERT INTO @MyTable VALUES (1,'Mother Father Daughter Son')
INSERT INTO @MyTable VALUES (2,'Mother Daughter Son')
INSERT INTO @MyTable VALUES (3,'Mother Son')
INSERT INTO @MyTable VALUES (4,'Daughter Son')
INSERT INTO @MyTable VALUES (5,'Mother Father Son')
INSERT INTO @MyTable VALUES (6,'Son Daughter Father')
INSERT INTO @MyTable VALUES (7,'Mother Son')
INSERT INTO @MyTable VALUES (8,'Other Word')
INSERT INTO @MyTable VALUES (9,'Son Son Son Daughter')
INSERT INTO @MyTable VALUES (10,'Other Mother Son Daughter Father')

INSERT INTO @Keys VALUES ('Mother',1)
INSERT INTO @Keys VALUES ('Father',2)
INSERT INTO @Keys VALUES ('Son',3)
INSERT INTO @Keys VALUES ('Daughter',4)

DECLARE @nAllWords int
SELECT @nAllWords = COUNT(*) FROM @Keys

SELECT MyTable.*
FROM @MyTable MyTable
INNER JOIN (SELECT MyTable.Id
FROM @MyTable MyTable
INNER JOIN @Keys KeyWords ON MyTable.Searched LIKE '%'+KeyWords.Word +'%'
GROUP BY MyTable.Id
HAVING COUNT(*) = @nAllWords) Tbl1 ON MyTable.Id = Tbl1.Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 00:12:55

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources